update jsonb data in PostgreSQL using where condition

Question

My table "Item" has two columns id and value, value is a jsonb and its value is as

{
"id": "0030e363-bd50-4c68-a127-07c8a6a35c47",
"fields": [
    {
        "value": "14",
        "field_title": "Code"
    },
    {
        "value": "name1",
        "field_title": "Name"
    },
    {
        "value": "452",
        "field_title": "Title"
    }
]
}

How do I update value field in "fields" where "field_title" = "Code" ?

Barbaros Özhan · Accepted Answer · 2021-01-05 15:27:47Z

2

You can use JSONB_SET() function after extract the elements of the array fields while determining the respective indexes such as

WITH f AS
(
 SELECT ('{fields,'||idx-1||',value}')::text[] AS path
   FROM tab 
  CROSS JOIN JSONB_ARRAY_ELEMENTS(value->'fields') 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'field_title'='Code' 
)
UPDATE tab
   SET value = JSONB_SET(value,f.path,'"1444"',false)
  FROM f;

SELECT value FROM tab;

value
-------------------------------
{
"id": "0030e363-bd50-4c68-a127-07c8a6a35c47",
"fields": [
    {
        "value": "1444",
        "field_title": "Code"
    },
    {
        "value": "name1",
        "field_title": "Name"
    },
    {
        "value": "452",
        "field_title": "Title"
    }
]
}

Demo

edited Jan 5, 2021 at 15:27

answered Jan 5, 2021 at 15:02

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

update jsonb data in PostgreSQL using where condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related