postgresql complex jsonb update

Question

I need to update a jsonb object in PostgreSQL but am unable to identify the correct syntax for updating the same, below are the use cases for the same.

Data in table

Create table jsondata (id int, data jsonb);
INSERT INTO jsondata VALUES 
(1,'[{"cc": null, "subsectionid": 26}, {"cc": null, "subsectionid": 27}]');

SELECT * from jsondata;

Use case:

Want to update the value of cc where subsectionid =27

Query tried:

UPDATE jsondata
SET data = data || '{null: ["Lake Providence"]}'::jsonb
WHERE data->>'subsectionid' = '27'

Please help me to achieve this.

Instead of using a list of objects. can you use an array or object with tiems indexed by subsectionid? — Jasen, Mar 29 '23 at 04:04
Would be much easier to do this update if you stored the objects in separate rows, not in an array in a single row — Bergi, Mar 29 '23 at 07:03
Does this answer your question? [How to make the jsonb\_set index position dynamic](https://stackoverflow.com/questions/69245244/how-to-make-the-jsonb-set-index-position-dynamic) — Bergi, Mar 29 '23 at 07:08

score 1 · Answer 1 · answered Mar 29 '23 at 03:24

1

I hope somebody comes along with a simpler answer, but this shows how I would break down the jsonb and then build it back up for the update:

with invars as (
  select 27 as subsectionid, 'Lake Providence' as cc
), els as (
  select j.id, ae.rn, 
         case (ae.el->>'subsectionid')::int 
           when i.subsectionid then i.cc
           else ae.el->>'cc' 
         end as cc, 
         ae.el->>'subsectionid' as subsectionid
    from invars i
         cross join jsondata j
         cross join lateral jsonb_array_elements(j.data) with ordinality as ae(el, rn)
   where jsonb_path_exists(j.data, '$[*].subsectionid ? (@ == $x)', 
           jsonb_build_object('x', i.subsectionid))
), newjson as (
  select id, jsonb_agg(els order by rn) - 'id' - 'rn' as newdata
    from els
   group by id
)
update jsondata
   set data = n.newdata
  from newjson n
 where n.id = jsondata.id;

Fiddle

answered Mar 29 '23 at 03:24

Mike Organek

11,647
3
11
26

I hope so too, but I don't think postgresql has any sort of JSON value selectors, only key and index selwectors. – Jasen Mar 29 '23 at 04:03
I don't think there can be a really simple solution for this, so this may be the best possible answer. I would say that it is a design mistake to use JSON in the database if you want to update individual attributes. – Laurenz Albe Mar 29 '23 at 06:44
It gets simpler if you don't use a CTE for `invars`, and also if you use a subquery inside of the `UPDATE` clause instead of `GROUP BY` on a separate query. See my answer – Bergi Mar 29 '23 at 07:20

Zegarek · Answer 2 · 2023-03-29T07:17:53.147

1

Since your top-level jsonb entity is an array, you can filter out the element you want to update, or rather pick everything but that in a jsonb_path_query_array(), then add the replacement. @? operator (or jsonb_path_exists()) let you pinpoint your targets with wildcards in jsonpath: demo

UPDATE jsondata
SET data = jsonb_path_query_array(data,'$[*] ?(@.subsectionid<>27)') 
           || '{"subsectionid":27,"cc": ["Lake Providence"]}'::jsonb
WHERE data @? '$[*].subsectionid ?(@==27)';

edited Mar 29 '23 at 07:17

answered Mar 29 '23 at 06:43

Zegarek

6,424
1
13
24

Removing-then-adding has the downside of changing the order in the array – Bergi Mar 29 '23 at 07:36

score 0 · Answer 3 · answered Mar 29 '23 at 07:18

0

Use jsonb_array_elements on the data, add the property to the object(s) where you want, then jsonb_aggregate them back to an array.

UPDATE jsondata
SET data = (
  SELECT jsonb_agg(CASE element->>'subsectionid'
    WHEN '27' THEN element || '{"cc": ["Lake Providence"]}'::jsonb
    ELSE element
  END)
  FROM jsonb_array_elements(data) AS element
)
WHERE id = 1; -- or data @> '[{"subsectionid":27}]'::jsonb;

answered Mar 29 '23 at 07:18

Bergi

630,263
148
957
1,375

It comes at the price of having to iterate through all elements in each `data`, but if there can be more than one element with `"subsectionid":27` in a single `data` and/or any of the elements could have other fields except `cc` and `subsectionid`, this still works, handling both of the cases - [unlike my answer](https://stackoverflow.com/a/75873938/5298879). – Zegarek Mar 29 '23 at 07:24
@Zegarek How can you not iterate through all elements in the array? Postgres has to built a new one anyway. – Bergi Mar 29 '23 at 07:34

score 0 · Answer 4 · answered Mar 29 '23 at 07:37

json_set function.

CREATE TABLE jsondata (
    id int,
    data jsonb
);

INSERT INTO jsondata
    VALUES (1, '[{"cc": null, "subsectionid": 26},  {"cc": null, "subsectionid": 27}]');

INSERT INTO jsondata
    VALUES (2, '[{"cc": 1, "subsectionid": 26},     {"cc": "world", "subsectionid": 27}]');

WITH cte1 AS (
    SELECT
        id,
        (jsonb_array_elements(t.data)) ->> 'subsectionid' AS subsectionid
    FROM
        jsondata t
),
cte2 AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id) - 1 AS ord
    FROM
        cte1
),
cte3 AS (
    SELECT
        *,
        string_to_array(ord || ',cc', ',')
    FROM
        cte2
    WHERE
        subsectionid = '27')
UPDATE
    jsondata
SET
    data = jsonb_set(data, string_to_array, '"Lake Providence"', FALSE)
FROM
    cte3
WHERE
    cte3.id = jsondata.id
RETURNING
    *;

main idea from:
select jsonb_set('[{"cc": null, "subsectionid": 26}, {"cc": null, "subsectionid": 27}]', '{1,cc}', '"Lake Providence"', false);

postgresql complex jsonb update

4 Answers4