How to update value in nested json Postgres

Question

I have following JSON stored in "Info" column

{
  "customConfig": {
    "isCustomGoods": 1
  },
  "new_addfields": {
    "data": [
      {
        "val": {
          "items": [
            {
              "Code": "calorie",
              "Value": "365.76"
            },
            {
              "Code": "protein",
              "Value": "29.02"
            },
            {
              "Code": "fat",
              "Value": "23.55"
            },
            {
              "Code": "carbohydrate",
              "Value": "6.02"
            },
            {
              "Code": "spirit",
              "Value": "1.95"
            }
          ],
          "storageConditions": "",
          "outQuantity": "100"
        },
        "parameterType": "Nutrition",
        "name": "00000000-0000-0000-0000-000000000001",
        "label": "1"
      },
      {
        "name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
        "val": "true"
      }
    ]
  }
}

I want to update value of nested json

{
  "name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
  "val": "true"
}

and set "val"to "Yes" str so the result should be like

{
  "name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
  "val": "Yes"
}

How can i do that ? Assuming that i need to update this value in json for many records in database

Whether your JSON structure is fixed or it can be changed

Akhilesh Mishra
– Akhilesh Mishra

2020-10-28 08:41:00 +00:00
Commented Oct 28, 2020 at 8:41 — Akhilesh Mishra
– Akhilesh Mishra, Commented Oct 28, 2020 at 8:41

the_spectator · Accepted Answer · 2020-10-28 09:16:21Z

6

We can use jsonb_set() which is available from Postgres 9.5+

From Docs:

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

Query to update the nested object:

UPDATE temp t
SET info = jsonb_set(t.info,'{new_addfields,data,1,val}', jsonb '"Yes"')
where id = 1;

It can also be used in select query:

SELECT 
  jsonb_set(t.info,'{new_addfields,data,1,val}', jsonb '"Yes"')
FROM temp t
LIMIT 1;

answered Oct 28, 2020 at 9:16

the_spectator

1,66317 silver badges28 bronze badges

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

Comments

Akhilesh Mishra · Accepted Answer · 2020-10-28 09:23:06Z

2

Considering you have a constant JSON Structure and a primary key in your table. Idea is to get the exact path of element val having value true (which can be at any index in the array) then replace it with desired value. So you can write your query like below:

with cte as (
select 
  id, 
  ('{new_addfields,data,'||index-1||',val}')::text[] as json_path
from 
  test, 
  jsonb_array_elements(info->'new_addfields'->'data') 
  with ordinality arr(vals,index) 
where 
  arr.vals->>'val' ilike 'true'
  )

 update test 
 set info = jsonb_set(info,cte.json_path,'"Yes"',false) 
 from cte 
 where test.id=cte.id;

DEMO

answered Oct 28, 2020 at 9:23

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to update value in nested json Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related