CODEWITHSUNDEEP
jsonamazon-web-servicesamazon-redshift
fez
fez

Reputation: 1835

Querying JSON Strings in AWS Redshift

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;

Upvotes: 3

Views: 1976

Answers (1)

GuiSim
GuiSim

Reputation: 7559

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.

Upvotes: 3

Related Questions