jsonpostgresqljson-extract

How to get complete record element from PostgreSQL json_to_recordset() Function?

I want to extract the complete record elements from json_to_recordset(). How to get it. Below is the description of my problem statement:

Description

I have one column with JSON Type and with the below value where each element of JSON Array are not necessarily has the same format. I want to convert this JSON Array into Table rows.

I have tried json_to_recordset() function available and I am also able to extract single elements from a row such as an id or name etc but not complete row.

[{"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34},
    {"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}]

My use case require to store the complete row as a single column how to get it in postgres.

Below logic, I have applied for a single field

SELECT json_to_recordset( data::json) as r(  id text, name text)

The output I am getting:

enter image description here

Expected Output

enter image description here

Solution

  • You can do it using json_array_elements to Expands a JSON array to a set of JSON values :

    ->> to get JSON object field as text

    select row->>'id' as id, row->>'name' as name, row
FROM mytable, json_array_elements(myjson) row;

    Result :

    id  name    row
1   AV  {"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34}
2   Ram {"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}

    Demo here