Insert Array of JSON into Postgres Table – How to Guide

database-designpostgresqlstored-procedures

PART 1:

I am using Postgres 9.5, and I am trying to figure out how to INSERT into a postgres table using an array of JSON. I have created a table with the following commands:

CREATE TABLE inputtable (
data_point_id SERIAL PRIMARY KEY NOT NULL,
chart_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
properties jsonb NOT NULL
);

The json data format looks like this:

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

Due to some changes, I now must be able to insert a json object per row based on user_id. In the end, I want the output to look like this:

data_point_id  | chart_id | user_id |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 1, "col3": 1, "col4": "one"}
    2          |    1     |     1    | { "col1": "b", "col2": 2, "col3": 2, "col4": "two"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "three"}

I tried to INSERT the data into the table by using unnest, where :

INSERT INTO inputtable (user_id, chart_id, properties) 
    SELECT (1, 1, unnest('{ "col1": "a", "col2": 1, "col3": 1, "col4": "one"},{ "col1": "b", "col2": 2, "col3": 2, "col4": "two"},{ "col1": "c", "col2": 3, "col3": 3, "col4": "three"}')::json)

but I get an error about type ERROR: could not determine polymorphic type because input has type "unknown".

PART 2:

I would also like to know how a table like this can be updated. For example, I make changes to the data point, in the JSON format, and want to change the properties, and I expect an output of:

data_point_id  | chart_id | user_id  |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 6, "col3": 7, "col4": "eight"}
    2          |    1     |     1    | { "col1": "b", "col2": 10, "col3": 11, "col4": "twelve"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "new"}

Using data like so:

[
    { col1: a, col2: 6, col3: 7, col4: eight},
    { col1: b, col2: 10, col3: 11, col4: twelve},
    { col1: c, col2: 3, col3: 3, col4: new}
]

How do can this be accomplished? I am thinking the part 2 question can be solved by jsonb_populate_recordset, but I am not sure. Using Postgres for JSON is new to me, but it looks very powerful and I appreciate the assistance in helping me figure this out!

Best Answer

PART1 - INSERTING

You don't have to use unnest() but jsonb_array_elements() and add square brackets to the JSON data structure. I sugget you using a JSON validator website like JSONlint to test the correctness of your JSON data.

This codes inserts 3 new records in inputtable:

WITH json_array AS (
    SELECT 1 AS user_id, 
           2 AS chart_id,
           jsonb_array_elements('
               [
                 {
                    "col1": "a",
                    "col2": 1,
                    "col3": 1,
                    "col4": "one"
                 }, {
                    "col1": "b",
                    "col2": 2,
                    "col3": 2,
                    "col4": "two"
                 }, {
                    "col1": "c",
                    "col2": 3,
                    "col3": 3,
                    "col4": "three"
                }
               ]'::jsonb) AS properties
)
INSERT INTO inputtable (user_id, chart_id, properties) 
SELECT * FROM json_array

PART2 - UPDATING

In order to update you must specify data_point_id values, so you have to know them a priori.

This works perfectly, but probably there are other naive solutions:

WITH update_table AS(
   SELECT unnest(ARRAY[1, 2, 3]) AS data_point_id,
          jsonb_array_elements('
            [
              {
                "col1": "a",
                "col2": 6,
                "col3": 7,
                "col4": "eight"
              }, {
                "col1": "b",
                "col2": 10,
                "col3": 11,
                "col4": "twelve"
              }, {
                "col1": "c",
                "col2": 3,
                "col3": 3,
                "col4": "new"
              }
           ]'::jsonb) AS properties 
    FROM inputtable
)
UPDATE inputtable 
SET properties = update_table.properties
FROM update_table
WHERE inputtable.data_point_id = update_table.data_point_id
Related Question