How can I parse JSON arrays in postgresql?

Question

I am using PostgreSQL 9.5.14, and have a column in a table that contains JSON arrays that I need to parse for their contents.

Using a select I can see that the structure of the JSON is of this kind:

SELECT rule_results from table limit 5;

Result:

[{"rule_key":"applicant_not_lived_outside_eu"},{"rule_key":"family_assets_exceed_limit"},{"rule_key":"owned_a_deed"}]

[]

[]

[{"rule_key":"family_category","details":"apply_with_parents_under_25"}]

[]

I have been unable to create an SQL command to give me the values of the rule_key keys.

I've attempted to use the documentation for json-functions in postgresql to find a solution from https://www.postgresql.org/docs/9.5/functions-json.html

SELECT rule_results::json->'rule_key' as results from table;

This gives me null values only.

SELECT jsonb_object_keys(rule_results::jsonb) from table;

This results in the error msg "cannot call jsonb_object_keys on a scalar", which seems to mean that the query is limited to a single row.

This looks simple enough, an array with key:value pairs, but somehow the answer eludes me. I would appreciate any help.

What result are you expecting? Every value in one row? All values in one array? — S-Man
– S-Man, Commented May 20, 2019 at 8:57
I am hoping to be able to have every value in one row. At the moment I am trying to figure out how to get to the key pairs inside the array. — Lauri
– Lauri, Commented May 20, 2019 at 9:00

S-Man · Accepted Answer · 2019-05-20 09:03:53Z

4

demo: db<>fiddle

Different solutions are possible. It depends on what you are expecting finally. But all solutions would use the function json_array_elements(). This expands every element into one row. With that you can do whatever you want.

This results in one row per value:

SELECT 
    value -> 'rule_key'
FROM
    data,
    json_array_elements(rule_results)

answered May 20, 2019 at 9:03

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How can I parse JSON arrays in postgresql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related