A JSON array mapped as a tablejson | System og Metode Q&A

Submitted by Glenn Jarzomkowski on Wed, 07/15/2015 - 12:07

Forums

Hi,

I have an JSON array of objects in a DB/2 row, but desperately need them as straight up SQL rows so I can join it with other table. Is there a solution in the IceBreak JSON SQL engine for that?

R: A JSON array mapped as a tablejson

Hi GJ;

You are in luck: First you can pulle out the JSON array as a table with "jsonAsTableRow". This gives you a JSON object for each array element, however you can simply pull out each value now an give it nice DB/2 namves with the jsonGetInt() / jsonGetStr() functions.

With IceBreak on you library list; try this from an SQL prompt:

with myMap as ( Select value from table (jsonAsTableRow  ( '[ 
  { id:1, cust:"john"},                                       

  { id:2, cust:"Åge"}                                         

]','')) myVirtualMap                                          

)                                                             

select                                                        

  jsonGetInt(value  , 'id'  ) as id,                          

  jsonGetStr(value  , 'cust') as cust                         

from myMap

.. Your "myMap" table is good to use with joins and other sql features. You will now get:

Screenshot myMap

Best regards,

Niels Liisberg