1 Hstore style delete "-" operator for jsonb
2 ===========================================
4 PostgreSQL 9.4 intorduced the [jsonb](http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE)
5 type, but it'd be nice to be able to delete keys and pairs using the "-" operator
6 just like you can with the [hstore](http://www.postgresql.org/docs/9.4/static/hstore.html#HSTORE-OP-TABLE) type.
8 This sql script attempts to achieve that. E.g.
16 TEST=# \i pg_jsonb_delete_op.sql
38 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
47 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
55 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
68 TEST=# CREATE TABLE jsonb_test (a jsonb, b jsonb);
72 TEST=# INSERT INTO jsonb_test VALUES ('{"a": 1, "b": 2, "c": 3}', '{"a": 4, "b": 2}');
76 TEST=# SELECT * FROM jsonb_test WHERE a-b = '{"a": 1, "c": 3}'::jsonb;
78 --------------------------+------------------
79 {"a": 1, "b": 2, "c": 3} | {"a": 4, "b": 2}
89 TEST=# INSERT INTO jsonb_test
90 TEST-# SELECT ('{"a" : ' || i+1 || ',"b" : ' || i+2 || ',"c": ' || i+3 || '}')::jsonb,
91 TEST-# ('{"a" : ' || i+2 || ',"b" : ' || i || ',"c": ' || i+5 || '}')::jsonb
92 TEST-# FROM generate_series(1,1000) i;
96 TEST=# CREATE INDEX ON jsonb_test USING gin((a-b));
99 TEST=# EXPLAIN SELECT * FROM jsonb_test WHERE a-b @> '{"a": 1, "c": 3}';
101 -----------------------------------------------------------------------------------
102 Bitmap Heap Scan on jsonb_test (cost=20.26..24.52 rows=1 width=113)
103 Recheck Cond: ((a - b) @> '{"a": 1, "c": 3}'::jsonb)
104 -> Bitmap Index Scan on jsonb_test_expr_idx (cost=0.00..20.26 rows=1 width=0)
105 Index Cond: ((a - b) @> '{"a": 1, "c": 3}'::jsonb)