Remove Multiple Keys from Jsonb Column in One Statement

Remove multiple keys from jsonb column in one statement

That should be as simple as applying the #- operator multiple times:

SELECT '{ "a": 1, "b": 2, "c": 3 }'::jsonb #- '{a}' #- '{b}';

 ?column? 
----------
 {"c": 3}
(1 row)

Remove multiple key/value pairs in one postgresql statement

(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.

How to delete an array of keys from a Postgres jsonb object?

Use the - operator with an array of text on the right hand side:

SELECT '{"foo": true, "bar": false, "baz": true}'::jsonb
       - '{foo,bar}'::text[];

Remove key from multiple nested jsonb objects with unknown keys in PostgreSQL

The structure of the json column is an anti-pattern, I fully agree with the comment by @mvp:

... you should consider extracting your data from json and store it in real SQL table(s) instead.

If you are forced to play with the original data, use the function:

create or replace function remove_nested_object(obj jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value- key_to_remove)
    from jsonb_each(obj)
$$;

update my_table
set json_column = remove_nested_object(json_column, 'toDelete')
where json_column::text like '%"toDelete":%';

Remove long key value pairs in jsonb column in postgres with SQL

There is no built-in function for this. You will need to write your own.

Something along the lines:

create function remove_long_values(p_input jsonb, p_maxlen int)
  returns jsonb
as
$$
  select coalesce(jsonb_object_agg(e.ky, e.val), '{}')
  from jsonb_each(p_input) as e(ky,val) 
  where length(e.val::text) <= p_maxlen;
$$
language sql
immutable
parallel safe;

The above does not deal with nested key/value pairs! It only checks this on the first level.

Then use it in the query:

CREATE MATERIALIZED VIEW IF NOT EXISTS test_materialized_view 
AS
SELECT t1.id, t1.data1 || t1.data2 || remove_long_values(t2.data1,250) as "data"
FROM table_1 t1 
  LEFT JOIN table_2 t2 ON (...);

PostgreSQL jsonb - omit multiple nested keys

There is no way to shorten the expression. If your goal is to pass to the query a single array of keys to be deleted you can use jsonb_set() with jsonb_each():

with my_table(json_col) as (
values
    (jsonb '{"a": {"b":1, "c": 2, "d": 3}}')
)

select jsonb_set(json_col, '{a}', jsonb_object_agg(key, value))
from my_table
cross join jsonb_each(json_col->'a')
where key <> all('{b, d}')    -- input
group by json_col             -- use PK here if exists

    jsonb_set    
-----------------
 {"a": {"c": 2}}
(1 row)

The solution is obviously more expensive but may be handy when dealing with many keys to be deleted.

Remove multiple keys and values with its nested dictionary then arrange keys in this “0”,“1”,“2”,“3” order like it was before in Python

Solution for multiple keys to remove:

dct = {
    "0": {"course": "CSE", "password": "Amsal", "username": "1800101253"},
    "1": {"course": "CSE", "password": "Amsal2", "username": "1800101253"},
    "2": {"course": "CSE", "password": "Amsal3", "username": "1800101253"},
    "3": {"course": "CSE", "password": "Amsal4", "username": "1800101253"},
    "4": {"course": "CSE", "password": "fjfjgal", "username": "1800101255"},
    "5": {"course": "CSE", "password": "Amsal", "username": "1804959494"},
    "Total": 6,
}

to_remove = [0, 1, 2, 3]


lst = [dct[str(v)] for v in range(dct["Total"]) if v not in to_remove]
dct = {str(i): v for i, v in enumerate(lst)}
dct["Total"] = len(lst)

print(dct)

Prints:

{'0': {'course': 'CSE', 'password': 'fjfjgal', 'username': '1800101255'},
 '1': {'course': 'CSE', 'password': 'Amsal', 'username': '1804959494'},
 'Total': 2}

---

Or: To remove according username:

username_to_remove = "1800101253"

lst = [
    dct[str(v)]
    for v in range(dct["Total"])
    if dct[str(v)]["username"] != username_to_remove
]
dct = {str(i): v for i, v in enumerate(lst)}
dct["Total"] = len(lst)

PostgreSQL: Remove attribute from JSON column

Update: for 9.5+, there are explicit operators you can use with jsonb (if you have a json typed column, you can use casts to apply a modification):

Deleting a key (or an index) from a JSON object (or, from an array) can be done with the - operator:

SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

Deleting, from deep in a JSON hierarchy can be done with the #- operator:

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json object directly with json_object_agg().

Related: other JSON manipulations whithin PostgreSQL:

• How do I modify fields inside the new PostgreSQL JSON datatype?

Original answer (applies to PostgreSQL 9.3):

If you have at least PostgreSQL 9.3, you can split your object into pairs with json_each() and filter your unwanted fields, then build up the json again manually. Something like:

SELECT data::text::json AS before,
       ('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after
FROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data),
LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS l
GROUP BY data::text

With 9.2 (or lower) it is not possible.

Edit:

A more convenient form is to create a function, which can remove any number of attributes in a json field:

Edit 2: string_agg() is less expensive than array_to_string(array_agg())

CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
   FROM json_each("json")
   WHERE "key" <> ALL ("keys_to_delete")),
  '{}'
)::json
$function$;

With this function, all you need to do is to run the query below:

UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');

---

Related Topics