CODEWITHSUNDEEP
sqljsonpostgresql
ey dee ey em
ey dee ey em

Reputation: 8623

How to write an insert SQL statement that loop through each record in an array of objects and insert into a record's specific columns accordingly?

First of all, I wanted to figure out how to even write an array of objects (like in js) in sql statement, and I am found nothing on the internet...

I can certainly just repeating all the insert statement, but I really just want to loop through a dataset and inject them into a table for a set of columns with exactly the same insert statement with different value! But seems there is no way to do this if the dataset is too complicated like an array of objects? or do I have to just write multiple list of arrays to represent each column which is really silly.. no?

Thanks

Example of data set

[
  {
    name: 'abc',
    gender: 'male',
  },
  {
    name: 'bbc',
    gender: 'female',
  },
  {
    name: 'ccc',
    gender: 'male',
  },
]

and put them into a table with columns of

nameHere
genderThere

Upvotes: 0

Views: 363

Answers (2)

kiran gadhe
kiran gadhe

Reputation: 743

select * from json_each( (REPLACE( REPLACE( REPLACE( your_input,  '},{' , ' ' )  ,'[','{') ,']','}'))::json)

this will output a table 

  name | gender
  -----+-------
  abc  | male
  bcc  | female 
  ccc  | male

you can insert it in any table you want

Upvotes: 0

user330315
user330315

Reputation:

You can use jsonb_array_elements to extract each JSON from the array, then use that as the source for an INSERT:

create table x(name text, gender text);

insert into x (name, gender)
select t ->> 'name', t ->> 'gender'
from jsonb_array_elements(
'[
  {
    "name": "abc",
    "gender": "male"
  },
  {
    "name": "bbc",
    "gender": "female"
  },
  {
    "name": "ccc",
    "gender": "male"
  }
]'::jsonb) t;

Online example: http://rextester.com/GZF87679

Update (after the scope changed)

To deal with nested JSON structures, you need to combine the operator that returns jsonb -> with the one that returns "plain text":

insert into x (name, gender)
select t -> 'name' ->> 'first', t ->> 'gender'
from jsonb_array_elements(
'[
  {
    "name": {"first": "a", "last": "b"},
    "gender": "male"
  }
]'::jsonb) t;

More details about the JSON operators can be found in the manual

Upvotes: 2

Related Questions