Yes, it seems to be the typical copy & paste problem. Perhaps, they should have done the same mistake in the code and pasted in the missing functionality from another of their versions. 😋
Original Message:
Sent: Fri June 16, 2023 06:04 AM
From: Mark Barinstein
Subject: Problems using JSON_TABLE on DB2 v11.5
I'm afraid, that this is a mistake in the documentation.
I've never seen any example, when Db2 for LUW's SYSIBM.JSON_TABLE (and not SYSTOOLS.JSON_TABLE with very limited capabilities) function returns multiple rows.
------------------------------
Mark Barinstein
Original Message:
Sent: Fri June 16, 2023 04:17 AM
From: Gustavo Adolfo Hernández Quesada
Subject: Problems using JSON_TABLE on DB2 v11.5
Good morning Mark,
I saw your answer but I can't understand how the official documentation says the following text, but then doesn't allow to do it:
"The JSON_TABLE table function returns a result table from the evaluation of SQL/JSON path expressions. Each item in the result sequence of the row SQL/JSON path expression represents one or more rows in the result table."
With SYSTOOLS.JSON_TABLE I nearly obtain what I want, but can't specify the columns and finally create a malformed JSON Fragment with an item of the array per row so when I want to extract one property with JSON_VALUE doesn't work.
In short, we are migrating from another database to this one and it seems that we have not made the most convenient decision.
------------------------------
Gustavo Adolfo Hernández Quesada
Original Message:
Sent: Fri June 16, 2023 03:44 AM
From: Mark Barinstein
Subject: Problems using JSON_TABLE on DB2 v11.5
Hi Gustavo,
I've answered your similar question on another site, where a number of generic table functions for JSON array unnesting was initially published and mentioned in the answer.
In short:
Db2 for LUW at the moment has limited json processing functionality and doesn't support the `nested path` json expression unlike Db2 for IBM i.
We have to emulate this capability ourselves.
------------------------------
Mark Barinstein
Original Message:
Sent: Wed June 14, 2023 10:48 AM
From: Gustavo Adolfo Hernández Quesada
Subject: Problems using JSON_TABLE on DB2 v11.5
I have a table where I allocated a JSON document as string inside a CLOB field.
Inside that JSON, there is an array that I need to expose as table rows to can join in a SQL sentence.
Let me explain with an example.
Example JSON
{ "string":"string", "array":[{ "type":"main", "name":"name" },{ "type":"othertype", "name":"othername" }], "object":{ "type":"objecttype", "name":"objectname" }}
I read the official documentation and found that I can do that with JSON_TABLE function. The documentation have 2 different JSON_TABLE, one on built-in functions, SYSIBM package and the other one on SYSTOOLS package that seems to will be deprecated in future.
With SYSIBM.JSON_TABLE function I can extract, from the JSON field, properties such as string or string inside objects, but I can't extract the array as table rows (my main goal).
Here is the SQL sentence I'm trying to run (I put the JSON string directly as parameter to make it simpler):
select t.* from json_table('{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' FORMAT JSON, 'strict $' columns ( string varchar(20) path 'strict $.string', type varchar(20) path 'strict $.object.type', name varchar(20) path 'strict $.object.name', nested path 'strict $.array[*]' columns( type2 varchar(20) path 'strict $.type', name2 varchar(20) path 'strict $.name' ) ) error on error) as t where true;
And the error I'm obtaining is:
SQL0104N An unexpected token "path 'strict $.array[*]' columns(type2" was found following "object.name', nested". Expected tokens may include: "<space>". SQLSTATE=42601
If I remove the array specification from the SQL sentence run without problems (I received a table with the values: string, objecttype and objectname):
select t.* from json_table('{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' FORMAT JSON, 'strict $' columns ( string varchar(20) path 'strict $.string', type varchar(20) path 'strict $.object.type', name varchar(20) path 'strict $.object.name' ) error on error) as t where true;
On the other hand, I obtained another errors before I built that sentence:
- Not allow me to put lax instead of strict --> To avoid to return an error when the array is empty
- Not allow me to put NULL ON ERROR clause --> I need it for table joins to not throw an exception
I also checked with SYSTOOLS.JSON_TABLE but it doesn't work as I wanted, and also need a BSON as main source instead of string and I don't want to make conversions each time I need to look inside.
Anyone can help me?
Thanks in advance.
------------------------------
Gustavo Adolfo Hernández Quesada
------------------------------
