Remove jsonb array element by value

Question

I did figure out how to remove a value from an array for a single record, but how to do it for many of them. The problem is in the way how I use the subquery. As it has to return only single element. Maybe my approach is wrong.

    Given input: '{attributes:['is_new', 'is_old']}'
    Expected result '{attributes: ['is_old']}' #remove 'is_new' from jsonb array

    Real example:
    #   sku  |           properties 
    # -------+--------------------------------
    #  nu3_1 | {                             +
    #        |     "name": "silly_hodgkin",  +
    #        |     "type": "food",           +
    #        |     "attributes": [           +
    #        |         "is_gluten_free",     +
    #        |         "is_lactose_free",    +
    #        |         "is_new"              +
    #        |     ]                         +
    #        | }

#Query that removes single array element:

SELECT c.sku, jsonb_agg(el) FROM
catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku where el  'is_new'
GROUP BY c.sku;

#Update query that removes single array element in single record

UPDATE catalog SET properties=jsonb_set(properties, '{attributes}', (
    SELECT jsonb_agg(el) FROM
    catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku
    WHERE el  'is_new' AND c.sku='nu3_1'
    GROUP BY c.sku
    )
) WHERE sku='nu3_1';

The question again is. How to remove jsonb array element by value for many database records?

klin · Accepted Answer · 2020-10-13 11:51:24Z

44

Use jsonb_set() and the delete operator -:

update catalog
set properties = 
    jsonb_set(properties, '{attributes}', (properties->'attributes') - 'is_new');

Test it in db<>fiddle.

edited Oct 13, 2020 at 11:51

answered Nov 1, 2016 at 22:02

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

simpleman Over a year ago

I did try the '-' operator, before asking the question here but it did not work. What makes it work are the parentheses (properties->'attributes'). Without them it does not work. I do not get it. What is the difference? I get it now both '->' and '-' are operators. It looks like, that '-' has a higher priority and it tries to use it against two strings, which can not work. Thank You @klin.

N P Over a year ago

Is it possible to do a where in this? Say you had objects within an array that had an ID and do where ID = 1?

klin Over a year ago

@NickPocock - yes, you can add where clause like in any other UPDATE statement, see this example.

N P Over a year ago

How about for something like this? stackoverflow.com/questions/42299061/… Struggling massively!

Brook Over a year ago

Hi, @klin, If I have a field parents with content [123, 345, 789], can I remove an element say 123 with jsonb_set, or if there is any other solutions ?

|

Vao Tsun · Accepted Answer · 2016-10-28 08:27:26Z

2

So, I believe QRY you are looking for is:

with q as (
  select distinct sku, jsonb_set(properties,'{attributes}',jsonb_agg(el) over (partition by sku),false) new_properties
  from (
    select 
      sku, jsonb_array_elements_text(properties->'attributes') as el, properties
    from catalog
  ) p
  where el != 'is_new'
)
update catalog set properties = q.new_properties from q where catalog.sku = q.sku
;

be aware that I assumed your sku is at least UK!

answered Oct 28, 2016 at 8:27

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

1 Comment

Vao Tsun Over a year ago

I just rewrote the question qry so it would work .I would definetely prefer - operator myself

MacNetron · Accepted Answer · 2020-08-09 07:49:46Z

1

Your solution works + you pointed me to some new stuff like window functions and give me an idea for alternative solution:

WITH q as (
  SELECT c.sku as sku,  jsonb_set(properties, '{attributes}', jsonb_agg(el)) as new_properties
    FROM catalog c JOIN (select sku, jsonb_array_elements_text(properties->'attributes') as el from catalog) c2 ON c.sku=c2.sku where el != 'is_new'
    GROUP BY c.sku
)
UPDATE catalog SET properties=q.new_properties FROM q WHERE catalog.sku=q.sku;

edited Aug 9, 2020 at 7:49

MacNetron

111 silver badge6 bronze badges

answered Nov 1, 2016 at 21:34

simpleman

2351 gold badge2 silver badges8 bronze badges

Comments

Suraj Rao · Accepted Answer · 2021-09-30 07:47:43Z

0

as per documentation:

json_build_object ( VARIADIC "any" ) → json

jsonb_build_object ( VARIADIC "any" ) → jsonb

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb.

json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

update catalog
set properties = properties - 'attributes' || jsonb_build_object('attributes','[is_gluten_free,is_lactose_free,is_new]')
where properties ? 'attributes' 
returning *;

Now you can replace the key, also replace value. Just Make sure the attributes as top-level key.

edited Sep 30, 2021 at 7:47

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Sep 30, 2021 at 7:37

jian

5,0131 gold badge29 silver badges46 bronze badges

Collectives™ on Stack Overflow

Remove jsonb array element by value

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related