0

I have a data set that looks like this:

+---+------+--------+------------------------------------------------------------------------------+|id | Name | Mobile |                               jsonb_column                                   |+---+------+--------+------------------------------------------------------------------------------+| 1 | Name1|   123  |  [{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}] || 2 | Name2|   234  |  [{"2023-03-10": "false"}, {"2023-03-13": "false"}, {"2023-03-15": "false"}] || 3 | Name3|   456  |  [{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}] |+---+------+--------+------------------------------------------------------------------------------+

I was trying to update the json value using an sql statement where a particular mobile is present.

Expected Output

+---+------+--------+------------------------------------------------------------------------------+|id | Name | Mobile |                               jsonb_column                                   |+---+------+--------+------------------------------------------------------------------------------+| 1 | Name1|   123  |  [{"2023-03-01": "true"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}] || 2 | Name2|   234  |  [{"2023-03-10": "false"}, {"2023-03-13": "false"}, {"2023-03-15": "false"}] || 3 | Name3|   456  |  [{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}] |+---+------+--------+------------------------------------------------------------------------------+

I Tried

UPDATE table SET jsonb_column = jsonb_set(json_column::jsonb, '{2023-03-01}',  CASE WHEN jsonb_column->>'2023-03-01' = 'false' THEN 'true'::jsonb ELSE 'false'::jsonb END) where mobile = '123';

But that is throwing me an error like:

SQL Error [22P02]: ERROR: path element at position 1 is not an integer: "2023-03-01"

I saw multiple stackoverflow answers but can not figure out how to write the query.

  1. Update field in array of objects in json postgres
  2. Postgres: update specific json object in array
  3. Update json array in postgres

How do I write the query to update the value inside the array of json?

askedMar 20, 2023 at 20:34
abhi's user avatar

1 Answer1

0

When you need to update jsons regularly, you should urgently thing about normalising you data, and get rid of the json

CREATE TABLE testdata    ("id" int, "Name" varchar(5), "Mobile" int, "jsonb_column" jsonb);    INSERT INTO testdata    ("id", "Name", "Mobile", "jsonb_column")VALUES    (1, 'Name1', 123, '[{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}]'),    (2, 'Name2', 234, '[{"2023-03-10": "false"}, {"2023-03-13": "false"}, {"2023-03-15": "false"}]'),    (3, 'Name3', 456, '[{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}]');
WITH CTE AS  (SELECT "id", "Name", "Mobile",  CASE WHEN (value->>'2023-03-01') ::BOOLEAN  = false  THEN  jsonb_set(value,'{"2023-03-01"}','true'::jsonb,false)  ELSE jsonb_set(value,'{"2023-03-01"}','false'::jsonb,false) END value  FROM testdataCROSS JOIN LATERAL jsonb_array_elements("jsonb_column")  WHERE "Mobile" = 123),  CTE2 as (SELECT "id", "Name", "Mobile",jsonb_agg(value) as  "jsonb_column"FROM CTEGROUP BY "id", "Name", "Mobile")UPDATE testdataSET "jsonb_column" = CTE2."jsonb_column"FROM CTE2WHERE testdata.id = cte2.id
UPDATE 1
SELECT * FROM testdata
idNameMobilejsonb_column
2Name2234[{"2023-03-10": "false"}, {"2023-03-13": "false"}, {"2023-03-15": "false"}]
3Name3456[{"2023-03-01": "false"}, {"2023-03-03": "false"}, {"2023-03-05": "false"}]
1Name1123[{"2023-03-01": true}, {"2023-03-03": "false"}, {"2023-03-05": "false"}]

fiddle

answeredMar 20, 2023 at 21:34
nbk's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy 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.