2

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!

3

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
  • I had tried the update portion, and it says that it ran, but with 0 rows affected. Is something missing? – unseen_damage Oct 14 '16 at 14:40
  • @unseen_damage you have to specify the right values for the column data_point_id. For example, if you want to update the rows which have data_point_id=1, data_point_id=2 and data_point_id=3, than you have to specify ARRAY[1, 2, 3] in the above query. That array must contain the right values and you have to manage them from the logic of your software. This is necessary due to the data definition you're imposing. – pietrop Oct 14 '16 at 14:47
  • Ah, I see what you mean. Thank you very much for sharing your experience and shedding light on this for me! It has most likely spared me of days worth of research! – unseen_damage Oct 14 '16 at 14:52
  • I'm glad it helped :) It was a good exercise for me. Be aware that heavy usage of JSON helps you for data retrieving and maintenance over time, but is hard to code into a relation DB like Postgres and makes you hard code. You gain and loose something. – pietrop Oct 14 '16 at 15:02
  • I have a similar issue. I have the data inside a JSON file. The data is a list of JSON objects. [{"col1":"val1", "col2":"val2"},...]. Right now I am using COPY to load the data. But that is inserting the entire data into a single cell. I might have millions of objects inside the list. What's an efficient way to do that? – Jeet Parekh Apr 5 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.