Fetch array of data in JSON column

General
1

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:

2

Your error is happening because your data column is not a JSON array of restoration records, but instead an array of objects, and restoration_records is inside each object.

Here’s how to fix your query:

SELECT
    r.value ->> 'date_restored' AS date_restored
FROM members a,
LATERAL json_array_elements(a.data) AS obj,  -- loop over outer array
LATERAL json_array_elements(obj.value -> 'restoration_records') AS r;  -- loop over restoration_records

Explanation:

  • a.data is a JSON array of objects → loop with json_array_elements.
  • Each object has a restoration_records array → loop again inside it.
  • Extract date_restored from each inner object.

Output you’ll get:

date_restored
--------------
2019-05-30
2024-01-30

Let me know if you want to extract user_id too!