postgresql - Postgres：从匿名jsonb数组元素中删除对象

Question

我有一个包含 2 个字段的表：

table documents
  docu_id     uuid
  attachments jsonb

该列的示例数据为attachments jsonb：

[
 {
    "size": 10,
    "attach_id": "d3a21f904068"
 },{
    "Size": 0.143,
    "attach_id": "5ba4b285565b"
 }
]

我已经看到了许多关于如何根据字段名称更新/删除 jsonb 的示例，但是是否可以从匿名数组中删除匿名对象，其中"attach_id" = "X" and "docu_id"="Y":

delete from documents
  where docu_id = "Y" 
    and
    where attachments @> '[{"attach_id": "X"}]'

标签： postgresqlpostgresql-9.3postgresql-9.4

Answer 1

好的，找到了解决方案，所以我在这里分享它，（rextester 链接http://rextester.com/YICZ86369）：

插入数据

  create table documents(docu_id text, attachments jsonb);
    insert into documents values
    ('001', 
    '[
      {
        "name": "uno",
        "id":"1"
      },
       {
        "name": "dos",
        "id":"2"
      },
       {
        "name": "tres",
        "id":"3"
      }
    ]'
    ),
    ('002', 
    '[
      {
        "name": "eins",
        "id":"1"
      },
       {
        "name": "zwei",
        "id":"2"
      }
    ]'
    );
    select * from documents;

解决方案

UPDATE documents
   SET attachments = attachments #- 
          array(
                    SELECT i
                      FROM generate_series(0, jsonb_array_length(attachments) - 1) AS i
                      WHERE (attachments->i->'id' = '"2"')
           )::text[] /* cast as text */
       where docu_id = '002';

select * from documents;