Postgres jsonb_insert Insert Only When Object Doesn't Already Exist

Question

I am trying to add items to a JSON array using jsonb_insert as:

jsob_insert(document, '{path,to,array,0}','{"key":"value"}')

This inserts {"key":"value"} to the head of array. However, I want to do this insert iff the JSON object {"key":"value"} does not already exist in the array. Currently, it just adds it to the array without caring about duplicacy (which is how it is intended to work). I am just wondering if there is a way to take care of duplicate records this way.

Mike Organek · Accepted Answer · 2022-07-10 13:05:08Z

1

Wrap that call in a case that checks whether the object already exists in the array:

with invars as (
  select '{
    "path": {
      "to": {
        "array": [
          {
            "key": "value"
          }
        ]
      }
    }
  }'::jsonb as document
)
select case 
         when jsonb_path_exists(document, '$.path.to.array[*].key ? (@ == "value")')
           then document
         else jsonb_insert(document, '{path,to,array,0}', '{"key": "value"}')
       end as desired_result,
       jsonb_insert(document, '{path,to,array,0}', '{"key": "value"}') as old_result
  from invars;

db<>fiddle here

answered Jul 10, 2022 at 13:05

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Postgres jsonb_insert Insert Only When Object Doesn't Already Exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related