IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-03-14T15:52:54Z by BME_BME
BME_BME
BME_BME
2 Posts

Pinned topic SQL/JSON_Table Decomposition without table name.

‏2019-03-14T14:26:04Z | sql/json_table

Hello, 

with the new SQL/JSON operators, it's  easy to parse a Json containing tables with DB2 SQL. For example:

{"data":[{"id":"_1001"},{"id":"_1002"},{"id":"_1003"},{"id":"_1004"}]}

We can use:

SELECTY.* FROMJSON_TABLE(

'{"data":[{"id":"_1001"},{"id":"_1002"},{"id":"_1003"},{"id":"_1004"}]}'

,'lax $'COLUMNS(

NESTED PATH 'lax $.data[*]'COLUMNS (ID VARCHAR(50) PATH '$.id'))) asy;

And we get: 

id

_1001

_1002

...

My problem is that the JSON that I use in input doesn't have a table name:

{[{"id":"_1001"},{"id":"_1002"},{"id":"_1003"},{"id":"_1004"}]}

And I haven't found the right syntax to parse this type of Json ..

Could you please help me?

 

  • B.Hauser
    B.Hauser
    320 Posts

    Re: SQL/JSON_Table Decomposition without table name.

    ‏2019-03-14T15:35:06Z  

    Not sure whether the JSON syntax you display is valid or not. If the JSON data always start with a curley bracket followed by an array, just add a Key Name manually ... and voila it can be parsed:

    The following statement should return what you want.

    Select *
       from JSON_TABLE('{"data": ' concat Substr('{[{"id":"_1001"},{"id":"_1002"},{"id":"_1003"},{"id":"_1004"}]}', 2), 
                       '$.data[*]'
                       Columns(Id VarChar(50) path '$.id')) x;
    

     

    Birgitta

    Updated on 2019-03-14T15:37:04Z at 2019-03-14T15:37:04Z by B.Hauser
  • BME_BME
    BME_BME
    2 Posts

    Re: SQL/JSON_Table Decomposition without table name.

    ‏2019-03-14T15:52:54Z  

    it's perfect.

    Thank you  / Vielen Dank.