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

Concatenate an array of strings to a JSONB array of strings without duplicates in PostgreSQL

In PostgreSQL, I have a table named users with data that looks like so:

id favorites
1 { "shapes": ["Triangle", "Square"] }
2 { "shapes": ["Rectangle", "Triangle"] }
… …

Where favorites is a JSONB column.

I want to add shapes "Rectangle" and "Circle" to all users’ favorite shapes but without duplication. The output table should look like so:

id favorites
1 { "shapes": ["Triangle", "Square", "Rectangle", "Circle"] }
2 { "shapes": ["Rectangle", "Triangle", "Circle"] }
… …

How do I do that in one query?

Limitations:

  • The favorites object contains other fields than shapes and therefore cannot be converted into an array of strings.
  • The update must be in one query
  • If the input array contains an element that is already in a row’s shapes array, it must append only the ones that are not already in shapes.
Tags:

2

Answers


  1. 0

    This can be done using jsonb_set and the operator ||, but before insert any data we need to check if it is already in the array or not, to do so we can use the operator @> :

    update mytable
  set favorites = case 
         when favorites->'shapes' @> '["Square"]' = false
              and favorites->'shapes' @> '["Circle"]' = false
              then jsonb_set(favorites, '{shapes}', (favorites->'shapes') || '"Square"'::jsonb || '"Circle"'::jsonb)
         when favorites->'shapes' @> '["Square"]' = false and favorites->'shapes' @> '["Circle"]' = true
              then jsonb_set(favorites, '{shapes}', (favorites->'shapes') || '"Square"'::jsonb)
         when favorites->'shapes' @> '["Square"]' = true and favorites->'shapes' @> '["Circle"]' = false
              then jsonb_set(favorites, '{shapes}', (favorites->'shapes') || '"Circle"'::jsonb)
         else favorites
       end

    Demo here

    This is an other solution more readable using jsonb_array_elements and jsonb_agg to make sure the new added elements not exists :

    with cte as (
  select id as new_id, jsonb_set(favorites, '{shapes}', jsonb_agg(distinct e)) as new_favourites
  from mytable, jsonb_array_elements(favorites-> 'shapes' || '"Square"'::jsonb || '"Circle"'::jsonb) as e
  group by id, favorites
)
Update mytable
SET favorites = new_favourites
FROM cte
where id = new_id

    Demo 2 here

    Login or Signup to reply.
  2. 0 
    WITH 
step1 as (
  SELECT id,UNNEST(
        ARRAY[json_array_elements_text(favorites::json->'shapes')] ||
        ARRAY['Rectangle','Circle']  -- your parameters here, can be expanded arbitarily
      ) as myfavorites
   FROM test
  ), 
step2 as (
    SELECT 
    test.id, array_agg(DISTINCT step1.myfavorites) as uniquevalues
    FROM test
    INNER JOIN step1 on test.id=step1.id
    GROUP BY test.id
),
step3 as (
    SELECT id as up_id, json_build_object('shapes',uniquevalues) as f
    FROM step2
)
UPDATE test
SET favorites=favorites || f::jsonb
FROM step3
WHERE id=up_id;

    Instead of the update, you can use for example "SELECT * FROM step1" to see what happens at each step.

    Step1: Unnest the shapes, and concat them with the parameters

    Step2: Keep only distinct

    Step3: Rebuild the json property

    Update step: Update just the property needed, without harming any other properties you may have stored in that column (credits: https://dba.stackexchange.com/a/295300/246773)

    Fiddle: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/8340

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

Back To Top
Search