CODEWITHSUNDEEP
arrayspostgresqljsonb
Sardothien
Sardothien

Reputation: 87

Remove multiple key/value pairs in one postgresql statement

folks! I have table Template with only 1 column from type JSON. column

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

I have to remove all headers like %authorization%, %token%, %apiSecret%.

I created the following query:

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%']));

Unfortunately my query does not work. Where could be the problem?

Upvotes: 0

Views: 337

Answers (1)

Edouard
Edouard

Reputation: 7065

(a) You cant't mix jsonb type with array type : column::jsonb - ARRAY(...) is not allowed.

(b) If you want to remove several key/value pairs from the same jsonb data, then you have to create a user-defined aggregate based on the jsonb #- operator :

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
) ;

Then you can iterate on the new aggregate within a query :

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

see the test result in dbfiddle.

Upvotes: 1

Related Questions