Json Arrays of objects PostgreSQL Table format

Question

I have a JSON file (array of objects) which I have to convert into a table format using a PostgreSQL query.

Follow Sample Data. "b", "c", "d", "e" are to be extracted as separate tables as they are arrays and in these arrays, there are objects

I have tried using json_populate_recordset() but it only works if I have a single array.

[{a:"1",b:"2"},{a:"10",b:"20"}]

I have referred to some links and codes.

jsonb_array_element example

postgreSQL functions

Expected Output

Sample Data: 

    { 
       "b":[ 
           {columnB1:value, columnB2:value},
           {columnB1:value, columnB2:value},
        ],
       "c":[
           {columnC1:value, columnC2:value, columnC3:value},
           {columnC1:value, columnC2:value, columnC3:value},
           {columnC1:value, columnC2:value, columnC3:value}
        ],
        "d":[
           {columnD1:value, columnD2:value},
           {columnD1:value, columnD2:value},
        ],
       "e":[
           {columnE1:value, columnE2:value},
          
        ]
    }

expected output

b should be one table in which columnA1 and columnA2 are displayed with their values. Similarly table c, d, e with their respective columns and values.

Expected Output

I don't understand "are supposed to be separate sheets" - relational databases have no "sheets". Please edit your question and add the expected output. It will probably help if you provide more than one row of sample input data. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented May 20, 2022 at 5:34
Sample data doesn't look like json format, is it supposed to be? — EoinS
– EoinS, Commented May 20, 2022 at 13:03
I have made some changes so it will be easy to understand. Please help me find some way around to extract these tables from Json file. — Jkolhe
– Jkolhe, Commented May 20, 2022 at 13:13
Yes I have checked, its a json file with multiple array of objects in it. — Jkolhe
– Jkolhe, Commented May 20, 2022 at 13:15

EoinS · Accepted Answer · 2022-05-20 17:54:22Z

You can use jsonb_to_recordset() but you need to unnest your JSON. You need to do this inline as this is a JSON Processing Function which cannot used derived values.

I am using validated JSON as simplified and formatted at end of this answer

To unnest your JSON use below notation which extracts JSON object field with the given key.

--one level
select '{"a":1}'::json->'a' 
result : 1
--two levels
select '{"a":{"b":[2]}}'::json->'a'->'b'
result : [2]

We now expand this to include json_to_recordset()

select * from 
json_to_recordset(
  '{"a":{"b":[{"f1":2,"f2":4},{"f1":3,"f2":6}]}}'::json->'a'->'b' --inner table b 
) 
as x("f1" int, "f2" int); --fields from table b

or using json_array_elements. Either way we need to list our fields. With second solution type will be json not int so you cant sum etc

with b as (select json_array_elements('{"a":{"b":[{"f1":2,"f2":4},{"f1":3,"f2":6}]}}'::json->'a'->'b') as jx)
select jx->'f1' as f1, jx->'f2' as f2 from b;

Output

f1  f2
2   4
3   6

We now use your data structure in jsonb_to_recordset()

select * from jsonb_to_recordset( '{"a":{"b":[{"columnname1b":"value1b","columnname2b":"value2b"},{"columnname1b":"value","columnname2b":"value"}],"c":[{"columnname1":"value","columnname2":"value"},{"columnname1":"value","columnname2":"value"},{"columnname1":"value","columnname2":"value"}]}}'::jsonb->'a'->'b') as x(columnname1b text, columnname2b text);

Output:

columnname1b    columnname2b
value1b         value2b
value           value

For table c

select * from jsonb_to_recordset( '{"a":{"b":[{"columnname1b":"value1b","columnname2b":"value2b"},{"columnname1b":"value","columnname2b":"value"}],"c":[{"columnname1":"value","columnname2":"value"},{"columnname1":"value","columnname2":"value"},{"columnname1":"value","columnname2":"value"}]}}'::jsonb->'a'->'c') as x(columnname1 text, columnname2 text);

Output

columnname1 columnname2
value   value
value   value
value   value

Sample JSON

{
  "a": {
    "b": [
      {
        "columnname1b": "value1b",
        "columnname2b": "value2b"
      },
      {
        "columnname1b": "value",
        "columnname2b": "value"
      }
    ],
    "c": [
      {
        "columnname1": "value",
        "columnname2": "value"
      },
      {
        "columnname1": "value",
        "columnname2": "value"
      },
      {
        "columnname1": "value",
        "columnname2": "value"
      }
    ]
  }
}

Jkolhe · Accepted Answer · 2022-05-20 19:12:50Z

Well, I came up with some ideas, here is one that worked. I was able to get one table at a time. https://www.postgresql.org/docs/9.5/functions-json.html

I am using json_populate_recordset.

The column used in the first select statement comes from a table whose column is a JSON type which we are trying to extract into a table.

The 'tablename from column' in the json_populate_recordset function, is the table we are trying to extract followed with b its columns and datatypes.

WITH input AS(
   SELECT cast(column as json) as a
   FROM tablename
)
SELECT b.*
FROM input c,
            json_populate_recordset(NULL::record,c.a->'tablename from column') as b(columnname1 datatype, columnname2 datatype)

Collectives™ on Stack Overflow

Json Arrays of objects PostgreSQL Table format

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related