在一个postgresql语句中删除多个键/值对

Question

各位！我有一个表模板，只有一列来自JSON类型。列

{
"name": "any name",
"headers": {
"Accept": ["application/json"],
"ApiSecret": ["keySecret==="],
"ApiSecretKey": ["key==="],
"X-Auth-Token": ["token=="],
"OAUTH-Token": ["token2=="],
"Authorization": ["basicAuth"]
},
"description": "any desc"
}

我必须删除所有标头，如%authority%、%token%、%apiSecret%。

我创建了以下查询：

UPDATE template as w
SET 
column = column::jsonb - ARRAY(select wf.elements
from (select jsonb_object_keys(column->'headers') as elements
from template) as wf
where LOWER(wf.elements) LIKE ANY(ARRAY['%authorization%','%token%','%apikey%'])); 

不幸的是，我的查询不起作用。问题出在哪里？

Answer 1

(a(不能将jsonb类型与数组类型混合：不允许使用column::jsonb - ARRAY(...)。

(b( 如果您想从同一个jsonb数据中删除几个键/值对，那么您必须基于jsonb#-运算符创建一个user-defined aggregate：

CREATE OR REPLACE FUNCTION jsonb_remove(x jsonb, y jsonb, p text[])
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$  SELECT COALESCE(x, y) #- p ; $$ ;
DROP AGGREGATE IF EXISTS jsonb_remove_agg(jsonb, text[]) ;
CREATE AGGREGATE jsonb_remove_agg(jsonb, text[])
( sfunc = jsonb_remove
, stype = jsonb
) ;

然后，您可以在查询中迭代新的聚合：

UPDATE template as w
SET 
column = l.elements
FROM
( SELECT id -- id is to be replaced by the primary key of table template
, jsonb_remove_agg(column, array['headers', wf.elements]) as elements
FROM template
CROSS JOIN LATERAL jsonb_object_keys(column->'headers') as wf(elements)
WHERE LOWER(wf.elements) LIKE ANY(ARRAY['%authorization%','%token%','%apikey%'])
GROUP BY id -- id is to be replaced by the primary key of table template
) AS l
WHERE w.id = l.id ; -- -- id is to be replaced by the primary key of table template

请参阅dbfiddle中的测试结果。