CODEWITHSUNDEEP
arraysjsonpostgresql
Lauri
Lauri

Reputation: 47

How can I parse JSON arrays in postgresql?

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.

Upvotes: 0

Views: 8390

Answers (1)

S-Man
S-Man

Reputation: 23766

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)

Upvotes: 4

Related Questions