CODEWITHSUNDEEP
postgresqljsonb
yzhao91
yzhao91

Reputation: 11

Update jsonb column to add a list of strings in one nested key

My jsonb column(properties) looks like this:

{
"key1" : "v1",
"key2" : "v2",
...

}

Now I need to update this column(properties) and add the following nested key/val in the column:

"Fruit":{
  "Apple":{
    "tags":[
      "color_is_green",
      "taste_good",
      "smell_like_XX"
  ]
 }
}

I wonder if there is an postgresql query that can directly update this column ?

I have tried : 

UPDATE <table> 
SET 
properties = jsonb_set(properties,'{"Fruit","Apple","tags"}','"color_is_green","taste_good","smell_like_XX"');

But it gave me an error.

[22P02] ERROR: invalid input syntax for type json Detail: Expected end of input, but found ",". Position: 106 Where: JSON data, line 1: "obs_eng_status_faild",...

Originally we take the whole properties column out, and convert it to java object, and add/remove properties in java code. But now due to large number of records in db, taking all properties out and save in memory cause a performance issue(out of memory),they come put with an idea that update those properties directory in query and see if it can save space.

Upvotes: 1

Views: 234

Answers (1)

klin
klin

Reputation: 121604

You can concatenate jsonb values.

select 
    '{"key1" : "v1", "key2" : "v2"}':: jsonb 
    ||
    '{"Fruit": {"Apple": {"tags": ["color_is_green", "taste_good", "smell_like_XX"]}}}'::jsonb

                                                   ?column?                                                    
---------------------------------------------------------------------------------------------------------------
 {"key1": "v1", "key2": "v2", "Fruit": {"Apple": {"tags": ["color_is_green", "taste_good", "smell_like_XX"]}}}
(1 row)

So you can update your table in this way:

update my_table
set properties = properties ||
    '{"Fruit": {"Apple": {"tags": ["color_is_green", "taste_good", "smell_like_XX"]}}}'::jsonb
where id = 1

However, you should remember that complex JSON structures make the data processing difficult and inefficient.

Upvotes: 1

Related Questions