'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?

arrayspostgresqljsonb


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions