Fetch array of data in JSON column

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