sql - Postgres : push unique object into an array of jsonb

Question

我有一个 jsonb 数组 appliances 如果对象不存在，我试图将其推送到其中。我正在尝试使用此查询

update home
   set appliances = appliances || 
       '{"name": "Television", "uuid": "f6847317-d6fc-476f-8439-3a81f169e9ad"}'
 where uuid = 'f5018a28-fd5f-43df-9d09-4e73da448823'
   and not 
      '{"name": "Television", "uuid": "f6847317-d6fc-476f-8439-3a81f169e9ad"}'::jsonb 
          < @ appliances;

但它抛出一个错误:

ERROR: operator does not exist: jsonb <@ jsonb[]

appliances 列的数据类型为 jsonb[] 。我怎样才能使这个查询正确？

Answer 1

PostgreSQL 数组上的 <@ 运算符和 || 运算符需要两个操作数都是数组，而不仅仅是其中之一。

update home
   set appliances = appliances || 
       ARRAY['{"name": "Television", "uuid": "f6847317-d6fc-476f-8439-3a81f169e9ad"}'::jsonb]
 where uuid = 'f5018a28-fd5f-43df-9d09-4e73da448823'
   and not 
      ARRAY['{"name": "Television", "uuid": "f6847317-d6fc-476f-8439-3a81f169e9ad"}'::jsonb] 
          <@ appliances;