Update Postgres Table with Array of JSON

I am using Postgres 9.5, and I am trying to figure out how to update a postgres table using an array of JSON. I want each object in the array to coorespond to a new row, with each key cooresponding to a column, and each value is the data to be inserted into the column. I am trying to do so with a function. Here the the data format:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three},
    { col1: d, col2: 8, col3: 4, col4: four},
]

Here is my expected output:

col1   (varchar)| col2 (integer) |   col3 (integer)   |   col4 (varchar)
-----------------+----------------+--------------------+------------------
    a            |  5             |     1              |    one
    b            |  6             |     2              |    two
    c            |  7             |     3              |    three
    d            |  8             |     4              |    four

Is there a built in postgres JSON function or operator that can do this for me? Or do I have to loop through the array and pull out each value, and pass it as an input? I know function below is wrong, but my goal is for the function to act similar to the following:

CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC (
arrayOfValues TEXT[]
)
RETURN VOID AS $
BEGIN
UPDATE table SET (col1, col2, col3, col4) = ($1) 
END;
$ LANGUAGE plpgsql;

If you know the desired column names (be it the same or different from the keys in the JSON structure, you can use json[b]_to_recordset():

SELECT * FROM jsonb_to_recordset('[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb) AS t (col1 text, col2 integer, col3 integer, col4 text);

 col1 │ col2 │ col3 │ col4  
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

As the documentation tells us,


Note: In json_populate_record, json_populate_recordset, json_to_record and json_to_recordset, type coercion from the JSON is "best effort" and may not result in desired values for some types. JSON keys are matched to identical column names in the target row type. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will simply be NULL.

If you already have a table to work with, json_populate_recordset() is an even better solution:

CREATE TABLE inputtable (col1 text, col2 integer, col3 integer, col4 text);

SELECT * FROM jsonb_populate_recordset(NULL::yourtable, '[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb);

 col1 │ col2 │ col3 │ col4  
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

Now updating the table itself may be done like this:

WITH source AS (SELECT * FROM jsonb_populate_recordset [...])
UPDATE yourtable
   SET col1 = s.col1, col2 = s.col2
  FROM source AS s
 WHERE col3 = s.col3;

In case it seems slow, it might make sense to not use the CTE, but a subquery in the FROM clause instead.

StackExchange Database Administrators Q#151479, answer score: 7