Querying varying JSON strings in AWS Redshift

Question

Is there a way in AWS Redshift to query all key/value pairs from a JSON string where each record has a different number of key/value pairs?

I.e. in the below example, the first animal has the 'location' attribute whereas the second does not. Using the json_extract_path_text function I can pick out attributes I know exist for both records but when trying to query the location it will fail:

   create table test.jsondata(json varchar(65000));
    insert into  test.jsondata(json) values ('{ "animal_id": 1, "name": "harry", "animal_type": "cat", "age": 2, "location": "oakland"}');
    insert into  test.jsondata(json) values ('{ "animal_id": 2, "name": "louie","animal_type": "dog", "age": 4}');

    select 
      json_extract_path_text(JSON,'animal_id') animal_id,
      json_extract_path_text(JSON,'name') name,
      json_extract_path_text(JSON,'animal_type') animal_type,
      json_extract_path_text(JSON,'age') age,
      json_extract_path_text(JSON,'location') location
    from test.jsondata
    order by animal_id;

ERROR: 42601: syntax error at or near "location"

Desired Outcome:

animal_id name animal_type age location
1 harry cat 2 oakland
2 louie dog 4 NULL

@vkp sorry yes updated query. Even without the trailing comma though I get the same error message — fez
– fez, Commented Apr 25, 2016 at 15:33

fez · Accepted Answer · 2016-04-25 15:50:42Z

0

Found that that json_extract_path_text works with ISNULL:

select 
      json_extract_path_text(JSON,'animal_id') animal_id,
      json_extract_path_text(JSON,'name') name,
      json_extract_path_text(JSON,'animal_type') animal_type,
      json_extract_path_text(JSON,'age') age,
      ISNULL(json_extract_path_text(JSON,'location'),NULL) location
    from test.jsondata
    order by animal_id;

edited Apr 25, 2016 at 15:50

answered Apr 25, 2016 at 15:36

fez

1,8453 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying varying JSON strings in AWS Redshift

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related