insert array of objects into table

Question

I have the following array:

arr = [{"a":1, "b": 2}, {"a": 1, "b": 3}]

Using some help from this post, I am trying to parse these elements like this:

CREATE OR REPLACE FUNCTION jsonb_arr2text_arr(_js JSONB)
  RETURNS text[] AS
  $func$
SELECT ARRAY(SELECT jsonb_array_elements_text(_js));
$func$
LANGUAGE sql IMMUTABLE;

This returns:

"{{""a"": 1, ""b"": 2},{""a"": 1, ""b"": 3}}"

Now I want to insert these values into a table, couple with 2 fkeys with each row insert:

FOR LOOP OVER ARR:
    INSERT INTO table VALUES (DEFAULT, somefkeyvalue, arr[i].a, arr[i].b);

How would I go about writing the above query to the real thing?

cachique · Accepted Answer · 2016-07-07 21:34:34Z

5

arr is a json array. right?
Using jsonb_array_elements expand it into a set of jsonb values. More json arrays in your particular example. Sort of unnest json array.
And then use reference (-> operator) to 'a' and 'b' keys to obtain your insert values.

Something like

INSERT INTO table (somefk, a_value, b_value)
SELECT
  'somefk',
  datajson->'a',
  datajson->'b'
FROM jsonb_array_elements('[{"a":1, "b": 2}, {"a": 1, "b": 3}]'::jsonb) AS t(datajson)

edited Jul 7, 2016 at 21:34

answered Jul 7, 2016 at 21:22

cachique

1,2811 gold badge13 silver badges19 bronze badges

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

1 Comment

Eklavya Chandra Over a year ago

I don't understand what should I need to actually do .? I have my NodeJS server which saves data into my table. I sent my array of objects but it returns me in another format (combination of ///""). Please someone help me doing this.

Collectives™ on Stack Overflow

insert array of objects into table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related