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
------------------------------