How to turn a json object into individual rows

Question

I have a table with a "rules" column that looks like this:

{"111_abc":0,"222_def":0,"333_abs":0}

I'd like to write a postgres query to turn this into something like this:

rules
111_abc
222_def
333_abs

I have tried several renditions of json_extract_path or json_array_element - no luck.

klin · Accepted Answer · 2019-05-28 23:13:36Z

1

Use the function jsonb_object_keys(), e.g.:

with my_table(rules) as (
values
    ('{"111_abc":0,"222_def":0,"333_abs":0}'::jsonb)
)

select jsonb_object_keys(rules) as rules
from my_table;

  rules  
---------
 111_abc
 222_def
 333_abs
(3 rows)

answered May 28, 2019 at 23:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

max Over a year ago

awesome - thank you. I'm betting this works, but I just realized I'm pulling from a datawarehouse that uses postgres version 8.0.2. I don't think json data types were introduced until version 9.2. I'll either have to work on updating the sql version, or find a way to write a udf.

Ajay · Accepted Answer · 2019-05-29 06:28:59Z

0

select * from json_each_text('{"111_abc":0,"222_def":0,"333_abs":0}')

answered May 29, 2019 at 6:28

Ajay

7744 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to turn a json object into individual rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related