Skip to content
Advertisement

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

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

The data is loaded into table a_json with:

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

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

and got

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

Advertisement

Answer

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

Demo on DB Fiddle:

col1       | col2          | col3 | col4          
:--------- | :------------ | ---: | :-------------
PrimKey1   | sometext      |    9 | ["texxt"]     
PrimKey500 | someothertext |    1 | ["texxtagain"]
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement