Update Json object in an array of json in postgres

Question

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.

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

score 0 · Answer 1 · answered Mar 20 '23 at 21:34

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 testdata
CROSS JOIN LATERAL jsonb_array_elements("jsonb_column")
  WHERE "Mobile" = 123),
  CTE2 as (
SELECT "id", "Name", "Mobile",jsonb_agg(value) as  "jsonb_column"
FROM CTE
GROUP BY "id", "Name", "Mobile")
UPDATE testdata
SET "jsonb_column" = CTE2."jsonb_column"
FROM CTE2
WHERE testdata.id = cte2.id

UPDATE 1

SELECT * FROM testdata

id	Name	Mobile	jsonb_column
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"}]
1	Name1	123	[{"2023-03-01": true}, {"2023-03-03": "false"}, {"2023-03-05": "false"}]

fiddle

Update Json object in an array of json in postgres

1 Answers1