How to modify or remove a specific JSON object from JSON array stored in jsonb column type in PostgreSQL using where clause?

Question

In my Postgres database, I have one of the table columns having jsonb datatype. In that column, I am storing the JSON array. Now, I want to remove or modify a specific JSON object inside the array.

My JSON array looks like

[
    {
        "ModuleId": 1,
        "ModuleName": "XYZ"
    },
    {
        "ModuleId": 2,
        "ModuleName": "ABC"
    }
]

Now, I want to perform two operations:

How can I remove the JSON object from the above array having ModuleId as 1?
How can I modify the JSON object i.e. change the ModuleName as 'CBA' whose ModuleId is 1?

Is there a way through which I could perform queried directly on JSON array?

Note: Postgres version is 12.0

user330315 · Accepted Answer · 2020-07-21 08:13:01Z

2

Both problems require unnesting and aggregating back the (modified) JSON elements. For both problems I would create a function to make that easier to use.

create function remove_element(p_value jsonb, p_to_remove jsonb)
  returns jsonb
as
$$
  select jsonb_agg(t.element order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx)
  where not t.element @> p_to_remove;
$$
language sql
immutable;

The function can be used like this, e.g. in an UPDATE statement:

update the_table
  set the_column = remove_element(the_column, '{"ModuleId": 1}')
where ...

For the second problem a similar function comes in handy.

create function change_value(p_value jsonb, p_what jsonb, p_new jsonb)
  returns jsonb
as
$$
  select jsonb_agg(
         case
           when t.element @> p_what then t.element||p_new
           else t.element
         end order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx);
$$
language sql
immutable;

The || operator will overwrite an existing key, so this effectively replaces the old name with the new name.

You can use it like this:

update the_table
  set the_column = change_value(the_column, '{"ModuleId": 1}', '{"ModuleName": "CBA"}')
where ...;

I think passing the JSON values is a bit more flexible then hardcoding the keys which makes the use of the function very limited. The first function could also be used to remove array elements by comparing multiple keys.

If you don't want to create the functions, replace the function call with the select from the functions.

answered Jul 21, 2020 at 8:13

user330315

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

3 Comments

Sunny Over a year ago

I had gone through various SO answers and some of them suggest using jsonb_set(). Will the above method be efficient than using jsonb_set()?

user330315 Over a year ago

The question isn't if jsonb_set() is more efficient, as it's not applicable to your problems. You can not use jsonb_set() for the first question at all, so that's out of the question. And you can't really use it for the second problem either, because you don't know the index of the element you are trying to change.

Sunny Over a year ago

I know that my question is not about jsonb_set(), was just asking the reason that why you hadn't used that function. Anyways, your answer worked like charm and thanks that you have created a function for both the operations as that would be reusable in future queries also.

Barbaros Özhan · Accepted Answer · 2020-07-21 15:01:31Z

For your both cases, consider using a subquery including dynamic logic to determine the index of the element which contains the value for ModuleId key equal to 1.

For the First case, use #- operator :

WITH s AS
(
 SELECT ('{'||idx-1||'}')::text[] AS path, jsdata
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata)
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'ModuleId'='1' 
)
UPDATE tab
   SET jsdata = s.jsdata #- path
  FROM s

and for the second case, use jsonb_set() function with path coming from thesubquery :

WITH s AS
(
 SELECT ('{'||idx-1||',ModuleId}')::text[] AS path
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'ModuleId'='1' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsdata,s.path,'"CBA"',false)
  FROM s

Demo

Collectives™ on Stack Overflow

How to modify or remove a specific JSON object from JSON array stored in jsonb column type in PostgreSQL using where clause?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related