I want to fetch all array of data in JSON column
SELECT
x.restored ->> 'date_restored' AS date_restored
FROM members a
CROSS JOIN LATERAL json_array_elements(a.data -> 'restoration_records') AS x(restored);
ERROR: cannot call json_array_elements on a scalar
Sample data
:
data [
{
restoration_records: [
{ "date_restored": "2019-05-30", "user_id":"aaa0000" },
{ "date_restored": "2024-01-30", "user_id":"aaa0001" }
]
}]
I want the output like this:
2019-05-30, 2024-01-30
Try this
SELECT
x.restored ->> 'date_restored' AS date_restored
FROM
members a
CROSS JOIN LATERAL
json_array_elements(COALESCE(a.data -> 'restoration_records', '[]'::json)) AS x(restored);
Basically, you are saying if the data is not a json array (i.e. a scalar (null, string, number, obj)) make it return an empty array.
You could also just validate that it is of the expected array type as part of the query, but I'd guess you just want an empty array?
If you'd rather check for the proper type first, just change the CROSS JOIN LATERAL to...
CROSS JOIN LATERAL
json_array_elements(a.data -> 'restoration_records') AS x(restored)
WHERE
json_typeof(a.data -> 'restoration_records') = 'array';
thus only returning valid data already stored as json arrays