Initial commit

[postgresql/pg_jsonb_delete_op] / README.md

diff --git a/README.md b/README.md
new file mode 100755 (executable)
index 0000000..54e3e76
--- /dev/null
+++ b/README.md
@@ -0,0 +1,110 @@
+Hstore style delete "-" operator for jsonb
+===========================================
+
+PostgreSQL 9.4 intorduced the [jsonb](http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE) 
+type, but it'd be nice to be able to delete keys and pairs using the "-" operator 
+just like you can with the [hstore](http://www.postgresql.org/docs/9.4/static/hstore.html#HSTORE-OP-TABLE) type.
+
+This sql script attempts to achieve that. E.g.
+
+Install
+-------
+
+Run the script
+
+```sql
+TEST=# \i pg_jsonb_delete_op.sql
+SET
+CREATE FUNCTION
+COMMENT
+CREATE OPERATOR
+psql:pg_jsonb_delete_op.sql:23: ERROR:  operator does not exist: jsonb - text[]
+CREATE FUNCTION
+COMMENT
+CREATE OPERATOR
+COMMENT
+CREATE FUNCTION
+COMMENT
+CREATE OPERATOR
+COMMENT
+```
+
+Usage
+-----
+
+E.g.
+
+```sql
+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
+```
+
+...
+
+
+```sql
+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:
+
+```sql
+
+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
+
+```