CODEWITHSUNDEEP
db2-luw
Pavan Kathi
Pavan Kathi

Reputation: 51

DB2 LUW - JSON_Table reading json array data into tabular format

I am trying to convert JSON array data into tabular format using Json_Table function. I tried to run the below query but I am getting the following errors:

SQL‬‎/‪JSON‬‎ ‪scalar‬‎ ‪required‬‎.‪‬‎.‪‬‎ ‪SQLCODE‬‎=‪‬‎-‪16413‬‎,‪‬‎ ‪SQLSTATE‬‎=‪2203F‬‎,‪‬‎ ‪DRIVER‬‎=‪4‬‎.‪19‬‎.‪56).

The same query is working fine when the number of elements in Employees array is 1, but not otherwise.

SELECT E."id", E."name"
FROM JSON_TABLE
(
  '{
  "Employees": [
    {
      "id": 1,
      "name": "Kathi"
    },
    {
      "id": 2,
      "name": "Pavan"
    }
  ]
}', 'strict $' COLUMNS
  ( 
    "id" INTEGER PATH 'strict $.Employees[*].id'
  , "name"  VARCHAR(20) PATH 'strict $.Employees[*].name'
  
  ) ERROR ON ERROR
) AS E;

Upvotes: 1

Views: 2759

Answers (3)

Bill Moon
Bill Moon

Reputation: 1

See Columns in Columns

Select E.id, E.name                            
  From JSON_Table(                             
         '{"Employees": [                      
            {"id":1, "name":"Me"},             
            {"id":2, "name":"Myself"},         
            {"id":3, "name":"I"}               
          ]}', '$'                             
         Columns(                              
           Nested '$.Employees[*]'             
             Columns (                         
               id   INTEGER     Path '$.id',   
               name VARCHAR(20) Path '$.name'  
             )                                 
         )                                     
       ) AS E

Upvotes: 0

Pavan Kathi
Pavan Kathi

Reputation: 51

Just for the benefit of others who are looking for some examples, i found below link in internet, example 3 basically helps with my case. We need to use JSON_Table from SysTools schema.

https://www.worldofdb2.com/profiles/blogs/convert-json-data-to-relational-format

Upvotes: 1

Mark Barinstein
Mark Barinstein

Reputation: 12339

JSON_TABLE produces 1 row only per 1 json document.
You may use the following only:

    "id" INTEGER PATH 'strict $.Employees[n].id'
  , "name"  VARCHAR(20) PATH 'strict $.Employees[n].name'

where n={0, 1} for an array of 2 elements as in your example.

Upvotes: 0

Related Questions