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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.