CODEWITHSUNDEEP
jsonamazon-redshift
Murugan S
Murugan S

Reputation: 67

AWS Redshift - Nested JSON Parsing

We are looking to parse a similar JSON data in redshift, the approach is to make use of super data type which will allow to parse nested JSON's in a easier manner, however while executing the below - we are not any records in the output

Tried different ways not able to get through - any pointers would be helpful.

 CREATE TABLE IF NOT EXISTS stage.string_json_tb
(
json_text VARCHAR(65535) ENCODE lzo
,jid INTEGER ENCODE az64
,json_super SUPER ENCODE zstd
)
DISTSTYLE AUTO

insert into stage.string_json_tb
(json_text, jid, json_super)
values ('{"r_nations":[
      {
         "n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {
         "n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      }
      
   ]}', '4', json_parse('{"r_nations":[
      {
         "n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {
         "n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      }
      
   ]}'))

select c.*, d  from stage.string_json_tb c, c.json_super d
          where jid =4

One more info, in reality the original json is something like follows, however per our understanding this is complex in nature to parse (given that the keys "1", "2" will be dynamic in nature) and hence better approach would be to provide an list of array elements as mentioned above. Need clarity on whether this is a correct understanding.

{"r_nations":{
      "1": {
         "n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      "2": {
         "n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      }
     }
   }

Upvotes: 0

Views: 1319

Answers (1)

Murugan S
Murugan S

Reputation: 67

Found the issue, my initial node in the input json was not an array, and hence this should be the query to traverse,

select e  from stage.string_json_tb c, c.json_super.r_nations e
          where jid =2

Thanks so much Bill Weiner for the question and also helping in resolving the original error related to leader and pointing in the right direction.

Upvotes: 0

Related Questions