CODEWITHSUNDEEP
javascriptsqljsonpostgresql
lattam
lattam

Reputation: 3

Insert JSON array of objects into postgreSQL using object keys as column names

I have application written in Javascript which uses postgreSQL (9.6) DB to store data. Is there a way how to insert Javascript object directly into DB ?

I created this example table:

CREATE TABLE people (
  "uuid" uuid PRIMARY KEY,
  "name" text NOT NULL,
  "phone" number
);

Javascript object in the app:

{
  uuid: 'df7c5fdc-d6b7-4b5e-ac3b-1228fac64a90',
  name: 'Martin',
  phone: 123456
}

Now I have the array of different people (array of objects above) and I managed to insert it to DB in this way:

let uuids = people.map(p => `'${p.uuid}'`) // adding quotes, otherwise syntax error
let names = people.map(p => `'${p.name}'`)
let phones = people.map(p => `'${p.phone}'`)

INSERT INTO people (
  "uuid",
  "name",
  "phone"
)
VALUES (
  UNNEST(ARRAY[${uuids}])::uuid,
  UNNEST(ARRAY[${names}])::text,
  UNNEST(ARRAY[${phones}])::number
)

Is there a way to do this more efficiently ? like in mongo db.collection.insertMany(array) ? Also, I'd like to omit some of the values and have them NULL, which is not possible in this example.

I was searching in PostgreSQL docs and there are several functions to work with json but I couldn't find any suitable for this.

Thank you for help.

Upvotes: 0

Views: 2159

Answers (1)

Shawn
Shawn

Reputation: 52579

Got a chance to test json_to_recordset():

INSERT INTO people("uuid", name, phone)
SELECT "uuid", name, phone
FROM json_to_recordset(?) AS x("uuid" uuid, name text, phone int);

where the placeholder should be bound to an array of objects like you describe (Except in proper JSON, of course).

sqlfiddle example

Upvotes: 2

Related Questions