0

c.data is jsonb object, like this: {programs:[{...},{...}]}

c.data->'programs' is jsonb array, like this [{a:1},{b:2},{c:3,d:4}] or null

Already tried this:

SELECT p
FROM Clients c , jsonb_array_elements(c.data->'programs') p

or this

SELECT p
FROM Clients c , jsonb_array_elements(COALESCE(c.data->'programs','[]'::JSONB)) p

but it shows an error:

[22023] ERROR: cannot extract elements from a scalar

how can I retrieve the object inside data->'programs' as rows of object?

2 Answers 2

1

You can use a JSON path instead, using the jsonb_path_query function.

SELECT p
FROM Clients c
CROSS JOIN LATERAL jsonb_path_query(c.data, '$.programs') p

db<>fiddle

0

Nevermind, found the answer, must filter the null first

SELECT p
FROM Clients c, jsonb_array_elements(c.data->'programs') p
WHERE (c.data->>'programs') <> 'null'
-- or LENGTH(c.data->>'programs') > 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.