按名称删除jsonb数组项 [英] Deleting a jsonb array item by name
问题描述
我有下表
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"] }');
我可以像这样向数组添加一个元素
and i can add an element to the array like this
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"');
我现在想删除具有已知索引或名称的数组项.
I now would like to delete an array item with a known index or name.
我如何按索引或名称删除flag_color
元素?
How would i delete a flag_color
element by index or by name?
更新
按索引删除
UPDATE country SET extra_info = extra_info #- '{flag_colours,-1}'
如何删除姓名?
推荐答案
由于数组不能以直接的方式直接访问项目,因此我们可以尝试通过嵌套->来以不同的方式进行处理.过滤元素->把东西缝在一起.我已经编写了一个代码示例,其中包含有序注释,以提供帮助.
As Arrays do not have direct access to items in a straightforward way, we can try to approach this differently through unnesting -> filtering elements -> stitching things back together. I have formulated a code example with ordered comments to help.
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;
如果您只想更新受影响的列而不重新创建主表,则可以:
In the case you would like to update only the affected column without recreating the main table, you can:
UPDATE country
SET extra_info=new_extra_info
FROM new_country
WHERE country.id = new_country.id;
我将其细分为两个查询以提高可读性;但是,您也可以使用子查询代替创建新表(new_country).
I have broken it down to two queries to improve readability; however you can also use a subquery instead of creating a new table (new_country).
使用子查询,它应该看起来像:
With the subquery, it should look like:
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起)过滤行:
Additionally, you may filter rows via (As of PostgreSQL 9.4):
SELECT *
FROM country
WHERE (extra_info -> 'flag_colours') ? 'red'
这篇关于按名称删除jsonb数组项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!