PostgreSQL 9.4 intorduced the jsonb type, but it’d be nice to be able to delete keys and pairs using the “-” operator; just like you can with the hstore type.
Fortunately postgres makes creating an operator really easy for us, so lets have a go at creating a delete operator for jsonb.
First lets try to create an operator just to delete one key passed as text. We need to start by creating a function for our operator, and the only way I can think to do this looking at the docs is to unwrap the json with jsonb_each, filter out the matches, and roll it all back up:
[postgres]
TEST=# CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text)
RETURNS jsonb AS
$BODY$
SELECT COALESCE(
(
SELECT (‘{‘ || string_agg(to_json(key) || ‘:’ || value, ‘,’) || ‘}’)
FROM jsonb_each(a)
WHERE key <> b
)
, ‘{}’)::jsonb;
$BODY$
LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION
TEST=# COMMENT ON FUNCTION jsonb_delete_left(jsonb, text) IS ‘delete key in second argument from first argument’;
COMMENT
[/postgres]
Once we’ve created our function, we just need to create the operator to use it:
[postgres]
TEST=# CREATE OPERATOR – ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text);
CREATE OPERATOR
TEST=# COMMENT ON OPERATOR – (jsonb, text) IS ‘delete key from left operand’;
COMMENT
[/postgres]
And we’re ready to go:
[postgres]
TEST=# SELECT ‘{"a": 1, "b": 2, "c": 3}’::jsonb – ‘b’::text;
?column?
——————
{"a": 1, "c": 3}
(1 row)
[/postgres]
Seems to work, lets now try to create one that will let us delete a set of keys passed as an array:
[postgres]
TEST=# CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
RETURNS jsonb AS
$BODY$
SELECT COALESCE(
(
SELECT (‘{‘ || string_agg(to_json(key) || ‘:’ || value, ‘,’) || ‘}’)
FROM jsonb_each(a)
WHERE key <> ALL(b)
)
, ‘{}’)::jsonb;
$BODY$
LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION
TEST=# COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS ‘delete keys in second argument from first argument’;
COMMENT
TEST=# CREATE OPERATOR – ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]);
CREATE OPERATOR
TEST=# COMMENT ON OPERATOR – (jsonb, text[]) IS ‘delete keys from left operand’;
COMMENT
TEST=# SELECT ‘{"a": 1, "b": 2, "c": 3}’::jsonb – ARRAY[‘a’,’b’];
?column?
———-
{"c": 3}
(1 row)
[/postgres]
Ok, so now lets create one to delete matching key/value pairs:
[postgres]
TEST=# CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
RETURNS jsonb AS
$BODY$
SELECT COALESCE(
(
SELECT (‘{‘ || string_agg(to_json(key) || ‘:’ || value, ‘,’) || ‘}’)
FROM jsonb_each(a)
WHERE NOT (‘{‘ || to_json(key) || ‘:’ || value || ‘}’)::jsonb <@ b
)
, ‘{}’)::jsonb;
$BODY$
LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION
TEST=# COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS ‘delete matching pairs in second argument from first argument’;
COMMENT
TEST=# CREATE OPERATOR – ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb);
CREATE OPERATOR
TEST=# COMMENT ON OPERATOR – (jsonb, jsonb) IS ‘delete matching pairs from left operand’;
COMMENT
TEST=# SELECT ‘{"a": 1, "b": 2, "c": 3}’::jsonb – ‘{"a": 4, "b": 2}’::jsonb;
?column?
——————
{"a": 1, "c": 3}
(1 row)
[/postgres]
Seems to work fine to me, lets try an index:
[postgres]
TEST=# \timing on
Timing is on
TEST=# CREATE TABLE jsonb_test (a jsonb, b jsonb);
CREATE TABLE
Time: 207.038 ms
TEST=# INSERT INTO jsonb_test VALUES (‘{"a": 1, "b": 2, "c": 3}’, ‘{"a": 4, "b": 2}’);
INSERT 0 1
Time: 39.979 ms
TEST=# SELECT * FROM jsonb_test WHERE a-b = ‘{"a": 1, "c": 3}’::jsonb;
a | b
————————–+——————
{"a": 1, "b": 2, "c": 3} | {"a": 4, "b": 2}
(1 row)
Time: 47.197 ms
TEST=# INSERT INTO jsonb_test
SELECT (‘{"a" : ‘ || i+1 || ‘,"b" : ‘ || i+2 || ‘,"c": ‘ || i+3 || ‘}’)::jsonb,
(‘{"a" : ‘ || i+2 || ‘,"b" : ‘ || i || ‘,"c": ‘ || i+5 || ‘}’)::jsonb
FROM generate_series(1,1000) i;
INSERT 0 1000
Time: 84.765 ms
TEST=# CREATE INDEX ON jsonb_test USING gin((a-b));
CREATE INDEX
Time: 229.050 ms
TEST=# EXPLAIN SELECT * FROM jsonb_test WHERE a-b @> ‘{"a": 1, "c": 3}’;
QUERY PLAN
———————————————————————————–
Bitmap Heap Scan on jsonb_test (cost=20.26..24.52 rows=1 width=113)
Recheck Cond: ((a – b) @> ‘{"a": 1, "c": 3}’::jsonb)
-> Bitmap Index Scan on jsonb_test_expr_idx (cost=0.00..20.26 rows=1 width=0)
Index Cond: ((a – b) @> ‘{"a": 1, "c": 3}’::jsonb)
(4 rows)
Time: 13.277 ms
[/postgres]
All seems to work as expected. I guess the one thing I’m not so certain about here is if any of this behaves correctly once we start getting nested json, but at first glance it doesn’t look too wonky to me:
[postgres]
TEST=# SELECT ‘{"a": 1, "b": 2, "c": 3, "d": {"a": 4}}’::jsonb – ‘{"d": {"a": 4}, "b": 2}’::jsonb;
?column?
——————
{"a": 1, "c": 3}
TEST=# SELECT ‘{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}’::jsonb – ‘{"a": 4, "b": 2}’::jsonb;
?column?
————————-
{"c": 3, "d": {"a": 4}}
(1 row
[/postgres]
Of course being written in sql these probably aren’t anywhere near as fast as the hstore equivalents which are written in C, so it’d be nice to see something in core postgres to do this.
