postgresql - 按名称删除 jsonb 数组项

Question

我有下表

CREATE TABLE country (
    id      INTEGER NOT NULL PRIMARY KEY ,
    name        VARCHAR(50),
    extra_info  JSONB
);
 
INSERT INTO country(id,extra_info)
VALUES (1, '{ "name" : "France", "population" : "65000000", "flag_colours": ["red", "blue","white"]}');
 
INSERT INTO country(id,extra_info)
VALUES (2, '{ "name": "Spain", "population" : "47000000", "borders": ["Portugal", "France"] }');

我可以像这样向数组中添加一个元素

UPDATE country SET extra_info = jsonb_set(extra_info, '{flag_colours,999999999}', '"green"', true);

然后像这样更新

UPDATE country SET extra_info = jsonb_set(extra_info, '{flag_colours,0}', '"yellow"');

我现在想删除一个具有已知索引或名称的数组项。

如何按索引或名称删除 flag_color 元素？

更新

按索引删除

更新国家 SET extra_info = extra_info #- '{flag_colours,-1}'

如何按名称删除？

Answer 1

由于数组无法以直接的方式直接访问项目，我们可以尝试通过取消嵌套 -> 过滤元素 -> 将事物拼接在一起来尝试不同的方法。我制定了一个带有有序注释的代码示例以提供帮助。

CREATE TABLE new_country AS
-- 4. Return a new array (for immutability) that contains the new desired set of colors
SELECT id, name, jsonb_set(extra_info, '{flag_colours}', new_colors, FALSE)
FROM country
       -- 3. Use Lateral join to apply this to every row
       LEFT JOIN LATERAL (
  -- 1. First unnest the desired elements from the Json array as text (to enable filtering)
  WITH prep AS (SELECT jsonb_array_elements_text(extra_info -> 'flag_colours') colors FROM country)
  SELECT jsonb_agg(colors) new_colors -- 2. Form a new jsonb array after filtering
  FROM prep
  WHERE colors <> 'red') lat ON TRUE;

如果您只想更新受影响的列而不重新创建主表，您可以:

UPDATE country
SET extra_info=new_extra_info
FROM new_country
WHERE country.id = new_country.id;

我将其分解为两个查询以提高可读性；但是，您也可以使用子查询而不是创建新表 (new_country)。

使用子查询，它应该是这样的:

UPDATE country
SET extra_info=new_extra_info
FROM (SELECT id, name, jsonb_set(extra_info, '{flag_colours}', new_colors, FALSE) new_extra_info
      FROM country
             -- 3. Use Lateral join to scale this across tables
             LEFT JOIN LATERAL (
        -- 1. First unnest the desired elements from the Json array as text (to enable filtering)
        WITH prep AS (SELECT jsonb_array_elements_text(extra_info -> 'flag_colours') colors FROM country)
        SELECT jsonb_agg(colors) new_colors -- 2. Form a new jsonb array after filtering
        FROM prep
        WHERE colors <> 'red') lat ON TRUE) new_country
WHERE country.id = new_country.id;

此外，您可以通过(从 PostgreSQL 9.4 开始)过滤行:

SELECT *
FROM country
WHERE (extra_info -> 'flag_colours') ? 'red'