Teacher: Birgitta Hauser

Title: Consuming JSON Documents and Web Services with SQL (SQL and JSON – Part 2 of 2)

Abstract: JSON becomes more and more important especially in RESTful web services the, use of XML declines in favor of JSON. With the JSON_TABLE table function JSON documents can be parsed, converted into a relational representation and data picked specifically out. Additional scalar functions allow SQL to directly access JSON documents located within the IFS (Integrated File System) or in source physical file members. Last but not least IBM provides SQL functions for getting access to web services. In composition with the JSON_TABLE function the JSON responses returned by web services can be consumed.

Learning Objectives:
Learn how JSON documents located in either the IFS or a column of a table or JSON answers returned by web services can be parsed with the JSON_TABLE table function, converted into a relational representation and data specifically selected. You’ll also get an overview over the new http-functions and how to use them for getting access to web services.

Audience: Developers and Users who are experienced in SQL Queries and need to process JSON documents located in either SQL tables or the IFS or JSON answers returned by web services or simply want to enhance their SQL know-how.
 

Level: Advanced

Duration: Single Session

Agenda:

• JSON Basics

• JSON Support in SQL for Db2 for i

• JSON_TABLE Table Function

  • JSON Path Expression

  • Converting JSON Data Types into SQL Data Types

  • (Output) Column Definition

    • Regular Column Definition

    • Nested Column Definition

    • Ordinality Columns

    • Returning JSON Formatted Data

  • Decomposing complex JSON Data with multiple nested Levels

• Accessing Web-Services and consuming the returned JSON Data

​