How to write a SQL query which uses the JSON format of table a_json to populate table b with its respective values in postgresql

Question

I have a file named data.json. The structure is like this:

 {  "PrimKey1": {    "layout": "normal",    "col1": "PrimKey1",   
 "__colll2__": "sometext",    "col3": 9, "OTHERCOLUMN":"dontneedthis", "col4": ["texxt"] }, 
 ... ,
 {"PrimKey500": {    "layout": "normal",    "col1": "PrimKey500",   
 "col2": "someothertext",    "col3": 1,  "col4": ["texxtagain"] }}

The data is loaded into table a_json with:

CREATE TABLE a_json (
  data json
);

\copy a_json FROM 'mypath/data.json/';

Since the table is not in the expected format, I create a new table named b.

CREATE TABLE b (
  col1      text PRIMARY KEY,
  col2 text,
  col3 numeric,
  col4 text
);

where the columns are named after the columns that I need from data.json.

Now, I want to insert everything from table a_json into b. I tried

INSERT INTO b
SELECT * from a_json json_each(data);

and got

ERROR: index row requires 1945656 bytes, maximum size is 8191

GMB · Accepted Answer · 2020-05-25 23:58:23Z

1

You can just use json_each() and json accessors for this:

insert into b(col1, col2, col3, col4)
select j.v ->> 'col1', j.v ->> 'col2', (j.v ->> 'col3')::numeric, j.v ->> 'col4'
from a_json a
cross join lateral json_each(a.data) j(k, v)

Demo on DB Fiddle:

col1       | col2          | col3 | col4          
:--------- | :------------ | ---: | :-------------
PrimKey1   | sometext      |    9 | ["texxt"]     
PrimKey500 | someothertext |    1 | ["texxtagain"]

edited May 25, 2020 at 23:58

answered May 25, 2020 at 23:18

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write a SQL query which uses the JSON format of table a_json to populate table b with its respective values in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related