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
midrange.com
