Question 89198 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql complex jsonb update

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.

Tags:

4

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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)';
    Login or Signup to reply.
  3. 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;
    Login or Signup to reply.
  4. 0

    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);

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search