Hstore style delete - operator for jsonb in PostgreSQL
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.gitignore
README.md
pg_jsonb_delete_op.sql

README.md

Hstore style delete "-" operator for jsonb

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.

This sql script attempts to achieve that. E.g.

Install

Run the script

TEST=# \i pg_jsonb_delete_op.sql
SET
CREATE FUNCTION
COMMENT
CREATE OPERATOR
COMMENT
CREATE FUNCTION
COMMENT
CREATE OPERATOR
COMMENT
CREATE FUNCTION
COMMENT
CREATE OPERATOR
COMMENT

Usage

E.g.

TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
     ?column?     
------------------
 {"a": 1, "c": 3}
(1 row)

Time: 2.290 ms


TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
 ?column? 
----------
 {"c": 3}
(1 row)

Time: 6.651 ms

TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
     ?column?     
------------------
 {"a": 1, "c": 3}
(1 row)

Time: 4.275 ms

...

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

In an index:

TEST=# INSERT INTO jsonb_test
TEST-# SELECT ('{"a" : ' || i+1 || ',"b" : ' || i+2 || ',"c": ' || i+3 || '}')::jsonb,
TEST-# ('{"a" : ' || i+2 || ',"b" : ' || i || ',"c": ' || i+5 || '}')::jsonb
TEST-# 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