I’ve started working with the DB2 for i JSON functionality for stored procedures. I have only been generating JSON output so far, but JSON_TABLE is next on my list.

For what it’s worth, the DB2 for i SQL reference has some info on JSON_TABLE. You can find a link called SQL reference at this page:
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzprintthis.htm
You have the option to change the version of operating system on this page, so you can get the SQL reference for your operating system (7.1, 7.2, 7.3 or 7.4). Here is the direct PDF link for 7.3:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzpdf.pdf

Someone had a question about what lax means. It’s one of two JSON path modes. If you use the strict mode, then an error is reported when the path cannot be used to navigate the JSON schema. The lax mode allows some errors. The SQL reference says:
lax
Specifies that certain structural errors are tolerated when navigating the
current JSON text. These include:
* automatic unnesting of arrays
* automatic wrapping of scalar values to be a one element array if
* referenced as an array
* specification of nonexistent items, including array index values that
are out of range
If an item does not exist, the SQL/JSON path expression returns an empty
string which is handled according to the current ON EMPTY clause.

Here are the examples from pp. 672-673 of the manual.


* These examples operate on the following JSON document:
{
"id" : 901,
"name" : { "first":"John", "last":"Doe" },
"phones": [ { "type":"home", "number":"555-3762"},
{ "type":"work", "number":"555-8792"}]
}


* List the employee id, first name, last name, and first phone type and number:

SELECT U."id", U."first name",U."last name",U."phone type",U."phone number"
FROM EMPLOYEE_TABLE E
JSON_TABLE(E.jsondoc,
’lax $’
COLUMNS( "id" INTEGER,
"first name" VARCHAR(20) PATH ’lax $.name.first’,
"last name" VARCHAR(20) PATH ’lax $.name.last’,
"phone type" VARCHAR(20) PATH ’lax $.phones[0].type’,
"phone number" VARCHAR(20) PATH ’lax $.phones[0].number’)
) AS U

Returns:
id first name last name phone type phone number
--- ---------- --------- ---------- ------------
901 John Doe home 555-3762


* List the employee id, first name, last name, and all available telephone types and numbers:

SELECT U."id", U."first name",U."last name",U."phone type",U."number" AS "phone number"
FROM EMPLOYEE_TABLE E
JSON_TABLE(E.jsondoc,
’lax $’
COLUMNS( "id" INTEGER,
"first name" VARCHAR(20) PATH ’lax $.name.first’,
"last name" VARCHAR(20) PATH ’lax $.name.last’,
NESTED PATH ’lax $.phones[*]’
COLUMNS (
"phone type" VARCHAR(20) PATH ’lax $.type’,
"number" VARCHAR(20) )
)
) AS U

Returns:
id first name last name phone type phone number
--- ---------- --------- ---------- ------------
901 John Doe home 555-3762
901 John Doe work 555-8792

Thanks,

Kelly Cookson


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.