git.8kb.co.uk Git - postgresql/pg_jsonb_delete_op/blob - 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 ```