Question 236452 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres json_to_recordset with a table

I’ve written a query that operates on a single JSON document (named ‘json_source’), and which returns each element of an array (named ‘codes’) within the JSON as a separate row, as follows:

SELECT
        json_array_elements(x.codes)->>'code'
    FROM
        json_to_recordset(json_source->'findings') AS x(
            codes json
        );

I have a database table with a JSON column that contains a document with the same structure as json_source. How can I rewrite the query above so that it operates on the table (i.e. with multiple rows of JSON data) rather than just on a single JSON document?

Tags:

2

Answers


  1. 0

    This might help:

    SELECT
    json_array_elements(t.json_column->'findings')->>'code'
FROM
    your_table t;
    Login or Signup to reply.
  2. 0

    You’d use json_to_recordset as a lateral table function call after selecting the rows from your table:

    SELECT
    json_array_elements(x.codes)->>'code'
FROM
    my_table mt,
    json_to_recordset(mt.json_column->'findings') AS x(codes json);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search