current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

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;
share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
    
So would the function look like this? CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC ( arrayOfValues TEXT[] ) RETURN VOID AS $$ BEGIN WITH source AS (SELECT * FROM jsonb_populate_recordset jsonb_populate_recordset(NULL::myTable, $1); UPDATE myTable SET (col1, col2, col3, col4) = (s.col1, s.col2, s.col3, s.col4) FROM source AS s END; $$ LANGUAGE plpgsql; – unseen_damage Oct 5 '16 at 14:20
    
@unseen_damage how about trying it? ;) – dezso Oct 5 '16 at 14:31
    
I just wanted to check and make sure the format was right. I got a syntax error: ERROR: syntax error at or near "NULL" using the above function. why would it fail? I also noticed I left off an S in my RETURN statement, but with that fixed, I get the error. – unseen_damage Oct 5 '16 at 14:40
    
ok I see my error from the first time around: double jsonb_populate_recordset. The error I get now is [error: function jsonb_populate_recordset(myTable, text[]) does not exist. Then when trying to do it manually, I get ERROR: invalid input syntax for type json..Does the JSON need to be quoted for it to work properly, for key/value pairs? – unseen_damage Oct 5 '16 at 14:57
    
One valid JSON can be seen in my answer - in any case, the quotes for keys and non-numeric values should be there. – dezso Oct 5 '16 at 15:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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