postgresql - 按名称删除 jsonb 数组项
问题描述
我有下表
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
按索引或名称删除元素?
更新
按索引删除
UPDATE country SET extra_info = extra_info #- '{flag_colours,-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'
推荐阅读
- kubernetes - Skaffold 以什么顺序启动部署,有没有办法指定顺序?
- cmake - 每次迁移到相同的新硬件时,如何避免重建 15GB-8 小时长的 PCL 库?
- python - Django Python Shell用十进制查询所有对象问题
- mongodb - 想要一些关于以下案例的 MongoDB 集合的最佳索引的输入
- python - 使用命令行 FreeCAD 创建一个简单的圆柱体
- linux - 针对 Cygwin 与 Linux 主机的交叉编译
- c# - 使用 oData 和 EFCore 返回关系对象
- css - 在 span 中引入 SVG 打破了子元素从左到右的定位
- regex - IntelliJ:将多行加入单个 CSV 行的正则表达式?
- python - 如果另一列中的值之间的差异足够小,如何填写数据框列的值?