CODEWITHSUNDEEP
jsonpostgresqljsonb
Rafa
Rafa

Reputation: 3339

Update jsonb column

I'm trying to update user facts inside of a column in a Postrgres db in the form designated here.

jsonb currently in data column and waiting to be updated:

data = { "type1" : ["value1", "value2"], "type2" : ["value3"], "type3" : ["value4"] }

jsonb that is sent up to db:

new_jsonb = { "type1" : ["value7", "value8"], "type3" : ["value6"]}

how the jsonb should look after it updates:

updated_jsonb = { "type1: ["value7", "value8"], "type2": ["value3"], "type3": ["value6"] }

The basic rules when comparing the value originally in the dataset, with the one we want to update it with are:

  1. if the new_jsonb that's being passed up contains keys that aren't in the current data jsonb, then those keys get added with their values.

  2. if the new_jsonb that's being passed up contains keys that are in the current data jsonb, then those keys all just replace the old keys.

This post did a useful job outlining the general use for the jsonb_set function, but I'm having trouble going through and making the comparison within the arrays. Specifically specifying the path parameter.

So basically where I'm stuck is not knowing what path to specify for this to happen. What I have is UPDATE table_name SET data = jsonb_set(data {don't know what path should be}, new) WHERE customer_id = $customerId. The new_jsonb that's passed up, won't always pass up the key-value pairs in the same order.

Upvotes: 2

Views: 381

Answers (1)

klin
klin

Reputation: 121524

Use the concatenate operator:

UPDATE table_name 
SET data = data || $newData
WHERE customer_id = $customerId;

Example: 

SELECT 
    '{ "type1": ["value1", "value2"], "type2": ["value3"], "type3": ["value4"] }'::jsonb ||
    '{ "type1": ["value7", "value8"], "type3": ["value6"]}'::jsonb as result

                                  result                                   
---------------------------------------------------------------------------
 {"type1": ["value7", "value8"], "type2": ["value3"], "type3": ["value6"]}
(1 row)

Upvotes: 3

Related Questions