3

I have a JSONB array below

[
  {"name":"test","age":"21","phone":"6589","town":"54"},
  {"name":"test12","age":"67","phone":"6546","town":"54"}
]

Now I want to update town,phone,age if name is test. How to update multiple values in JSONB array?

Improve this question
2
  • 2
    This would be so easy with a properly normalized data model. Commented Aug 10, 2020 at 12:25
  • Are you sure you want to store it like that? You might run in to issues later on. Even though a bit old, I would suggest you read this post on why it might be a bad idea. PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns Commented Aug 10, 2020 at 14:03

3 Answers 3

Reset to default
2

You can update them dynamically by indexing each individual element :

For age :

WITH s AS
(
 SELECT ('{'||idx-1||',age}')::text[] AS path
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'name'='test' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsdata,s.path,'"15"',false)
  FROM s

For town :

WITH s AS
(
 SELECT ('{'||idx-1||',town}')::text[] AS path
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'name'='test' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsdata,s.path,'"55"',false)
  FROM s

For phone :

WITH s AS
(
 SELECT ('{'||idx-1||',phone}')::text[] AS path
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'name'='test' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsdata,s.path,'"1111"',false)
  FROM s

Demo

Or directly at a time :

WITH s AS
(
 SELECT ('{'||idx-1||',phone}')::text[] AS path_phone,
        ('{'||idx-1||',town}')::text[] AS path_town,
        ('{'||idx-1||',age}')::text[] AS path_age
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'name'='test' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsonb_set(jsonb_set(jsdata,
                                              s.path_phone,
                                              '"1111"',
                                              false),
                                    path_town,
                                    '"55"',
                                    false),
                          s.path_age,
                          '"20"',
                          false)
      FROM s

Demo

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

4 Comments

Thanks for the quick response. can I update it in one query using jsonb_set? dbfiddle.uk/… I have found it this but here we can update menu_name only but in my case, i want to one or more value in object.
you're welcome @AvinashJagtap , I also added the case performing at a time.
Thank you very much. I will try this one. saved my day.
I have added another object in JSON trying to update. dbfiddle.uk/… instead of changing can we replace the whole object with an existing one?
1

This query is longer, but it should clarify expanding the original column to make the replacements:

with injson as (
  select '[
  {"name":"test","age":"21","phone":"6589","town":"54"},
  {"name":"test12","age":"67","phone":"6546","town":"54"}
]'::jsonb as jarray
), substitution as (
  select '{"name": "test", "age": "22", "phone": "6590", "town": "55"}'::jsonb as jnew
), expand as (
  select jsonb_array_elements(jarray) as jold
    from injson
), cond_update as (
  select coalesce(s.jnew, e.jold) as element
    from expand e
         left join substitution s
                on s.jnew->>'name' = e.jold->>'name'
)
select jsonb_agg(element) as result
  from cond_update;
                                                             result                                                             
--------------------------------------------------------------------------------------------------------------------------------
 [{"age": "22", "name": "test", "town": "55", "phone": "6590"}, {"age": "67", "name": "test12", "town": "54", "phone": "6546"}]
(1 row)

Based on the description of your table, it should look something like this:

with substitution as (
  select '{"name": "test", "age": "22", "phone": "6590", "town": "55"}'::jsonb as jnew
), expand as (
  select id, jsonb_array_elements("caloriesConsumption") as jold
    from "calorieTracker"
   where id = 1
), cond_update as (
  select id, coalesce(s.jnew, e.jold) as element
    from expand e
         left join substitution s
                on s.jnew->>'name' = e.jold->>'name'
)
update "calorieTracker" 
   set "caloriesConsumption" = cu.element
  from cond_update
 where cond_update.id = "calorieTracker".id;
Improve this answer

6 Comments

@AvinashJagtap If you want to see the update, then add your table information to your question.
[ {"name":"test","age":"21","phone":"6589","town":"54"}, {"name":"test12","age":"67","phone":"6546","town":"54"} ]
@AvinashJagtap That is not a table. That is only a JSON array. What is your database table name, and what columns are in it?
table name: calorieTracker and two column id and caloriesConsumption. this is my table infor in te table i have two columns id is int and caloriesConsumption is haveing jsob data type. What i am looking for i want to update id 1 and if c_date is forex:"08/08/2020" i want to update that object. I hope i have provided exat information.
@AvinashJagtap I added an example update statement to my answer.
|
1

Below query will give you results which contains test word in them. After you find these you can update any values in other columns.

Working Demo

 CREATE TABLE TEST2 (
    INFO JSON NOT NULL
);

INSERT INTO TEST2 (info)
VALUES('[
  {"name":"test","age":"21","phone":"6589","town":"54"},
  {"name":"test12","age":"67","phone":"6546","town":"54"},
  {"name":"dest147","age":"67","phone":"6546","town":"54"}
]');

SELECT *
FROM TEST2,
json_array_elements(info) elem
WHERE elem ->> 'name' like '%test%';
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.