Postgres jsonb_insert Insert Only When Object Doesn't Already Exist-CodePudding

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.

CodePudding user response：

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