All we need is an easy explanation of the problem, so here it is.
c.data is jsonb object, like this:
c.data->'programs' is jsonb array, like this
Already tried this:
SELECT p FROM Clients c , jsonb_array_elements(c.data->'programs') p
SELECT p FROM Clients c , jsonb_array_elements(COALESCE(c.data->'programs',''::JSONB)) p
but it shows an error:
 ERROR: cannot extract elements from a scalar
how can I retrieve the object inside
data->'programs' as rows of object?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can use a JSON path instead, using the
SELECT p FROM Clients c CROSS JOIN LATERAL jsonb_path_query(c.data, '$.programs') p
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂