Convert json dictionary column to key-value pair rows (Redshift+Postgresql)

Question

I have the following schema in redshift (an id column and a json dictionary as a string)

id data
1 {'1': 'true', '2':'false'}
2 {'1': 'false', '3':'true'}

and I would like to transform it to:

id key value
1 '1' 'true'
1 '2' 'false'
2 '1' 'false'
2 '3' 'true'

What would be an appropriate postgresql query for this? I have tried using JSON_EXTRACT_PATH_TEXT but then I need to union together all the individual keys and I am dealing with hundreds of keys.

cur4so · Accepted Answer · 2016-02-23 20:40:34Z

1

something like

select id,json_object_keys(t.data) as key,data->json_object_keys(t.data) as value from t;

http://sqlfiddle.com/#!15/781fd/10

edited Feb 23, 2016 at 20:40

answered Feb 23, 2016 at 20:14

cur4so

1,8204 gold badges21 silver badges29 bronze badges

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

1 Comment

Mirza Basim Baig Over a year ago

Unfortunately redshift uses postgresql 8.1 so the json function from later postgresql versions don't work. The above gives me the following error "[42883] ERROR: function json_object_keys("unknown") does not exist"

Collectives™ on Stack Overflow

Convert json dictionary column to key-value pair rows (Redshift+Postgresql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related