Querying JSON Strings in AWS Redshift

Question

I have a field varchar(65000) column in my AWS Redshift database which is used to store JSON strings. The JSON key/value pairs change frequently and I need to be able to run a daily report to retrieve all key/value data from the column.

For example:

create table test.json(json varchar(65000));
insert into test.json 
select '{"animal_id": 1, "name": "harry", "animal_type": "cat", "age": 2, "location": "oakland"}' union
select '{"animal_id": 2, "name": "louie","animal_type": "dog", "age": 4}' union 
select '{"animal_id": 3, "gender": "female"}' union
select '{"animal_id": 4, "size": "large"}' ;

With the above data I can write the below query to get the attributes I know are there however if a new attribute is added tomorrow, my report query will not pick up that new key/value pair. Is there any way to do a SELECT * type query on this table?

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,'location') location,
      json_extract_path_text(JSON,'age') age,
      json_extract_path_text(JSON,'gender') gender,
      json_extract_path_text(JSON,'size') size
    FROM test.json
    ORDER BY animal_id;

GuiSim · Accepted Answer · 2016-05-16 18:07:19Z

3

It is not possible to do what you want using your current schema with plain SQL.

If you can have application logic when creating your SQL query, you could dynamically create the SELECT statement.

Option A

Load the whole JSON in your app, parse it and obtain the required information this way.

Option B

When storing values in your database, parse the JSON object and add the discovered keys to another table. When querying your Redshift cluster, load this list of values and generate the appropriate SQL statement using this information.

Here's hoping these workarounds can be applied to your situation.

answered May 16, 2016 at 18:07

GuiSim

7,5596 gold badges43 silver badges50 bronze badges

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

1 Comment

fez Over a year ago

Thanks for your answer GuiSim, this is good to know. Option B will work for what I need :)

Collectives™ on Stack Overflow

Querying JSON Strings in AWS Redshift

1 Answer 1

Option A

Option B

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option A

Option B

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related