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

Question

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.

klin · Accepted Answer · 2019-10-18 06:54:42Z

1

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.

edited Oct 18, 2019 at 6:54

answered Oct 16, 2019 at 22:16

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yzhao91 Over a year ago

My goal is to insert the whole "Fruit" block into the original properties column. Is there any jsonb update query I can use ? The important thing is that I don't know how to insert an array of string into the "tags" field.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related