Infact there is no need to roll up values in where clause when deleteing by key
[postgresql/pg_jsonb_delete_op] / README.md
1 Hstore style delete "-" operator for jsonb
2 ===========================================
3
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.
7
8 This sql script attempts to achieve that. E.g.
9
10 Install
11 -------
12
13 Run the script
14
15 ```sql
16 TEST=# \i pg_jsonb_delete_op.sql
17 SET
18 CREATE FUNCTION
19 COMMENT
20 CREATE OPERATOR
21 psql:pg_jsonb_delete_op.sql:23: ERROR:  operator does not exist: jsonb - text[]
22 CREATE FUNCTION
23 COMMENT
24 CREATE OPERATOR
25 COMMENT
26 CREATE FUNCTION
27 COMMENT
28 CREATE OPERATOR
29 COMMENT
30 ```
31
32 Usage
33 -----
34
35 E.g.
36
37 ```sql
38 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
39      ?column?     
40 ------------------
41  {"a": 1, "c": 3}
42 (1 row)
43
44 Time: 2.290 ms
45
46
47 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
48  ?column? 
49 ----------
50  {"c": 3}
51 (1 row)
52
53 Time: 6.651 ms
54
55 TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
56      ?column?     
57 ------------------
58  {"a": 1, "c": 3}
59 (1 row)
60
61 Time: 4.275 ms
62 ```
63
64 ...
65
66
67 ```sql
68 TEST=# CREATE TABLE jsonb_test (a jsonb, b jsonb);
69 CREATE TABLE
70 Time: 207.038 ms
71
72 TEST=# INSERT INTO jsonb_test VALUES ('{"a": 1, "b": 2, "c": 3}', '{"a": 4, "b": 2}');
73 INSERT 0 1
74 Time: 39.979 ms
75
76 TEST=# SELECT * FROM jsonb_test WHERE a-b = '{"a": 1, "c": 3}'::jsonb;
77             a             |        b         
78 --------------------------+------------------
79  {"a": 1, "b": 2, "c": 3} | {"a": 4, "b": 2}
80 (1 row)
81
82 Time: 47.197 ms
83 ```
84
85 In an index:
86
87 ```sql
88
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;
93 INSERT 0 1000
94 Time: 84.765 ms
95
96 TEST=# CREATE INDEX ON jsonb_test USING gin((a-b));
97 CREATE INDEX
98 Time: 229.050 ms
99 TEST=# EXPLAIN SELECT * FROM jsonb_test WHERE a-b @> '{"a": 1, "c": 3}';
100                                     QUERY PLAN                                     
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)
106 (4 rows)
107
108 Time: 13.277 ms
109
110 ```