--- /dev/null
+.DS_Store
+Thumbs.db
+regression.diffs
+regression.out
+results/
--- /dev/null
+MODULE_big = jsonb_delete
+DATA = jsonb_delete--1.0.sql
+OBJS = jsonb_delete.o jsonb_delete_utils.o
+DOCS = README.md
+
+EXTENSION = jsonb_delete
+REGRESS = jsonb_delete
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
--- /dev/null
+jsonb_delete
+============
+
+Provides simple deletion of matching key-value pairs at the top level of PostgreSQL jsonb types.
+
+When the jsonb type was added in PostgreSQL 9.4 many modifying operators and functions were unimplemented, and although most operators have been <a href="https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#JSONB-modifying_operators_and_functions">added</a> in PostgreSQL 9.5 there is still no quick way of getting the difference of two jsonb types at the top level.
+
+* deletion using **-** operator
+* jsonb_delete(jsonb, jsonb)
+
+```sql
+CREATE EXTENSION jsonb_delete;
+```
+
+Was originally part of <a href="https://github.com/glynastill/pg_jsonb_opx">this</a> repository, but as of PostgreSQL 9.5 there's equivalent functionality of the other parts available in core (For PostgreSQL 9.4 you can get the 9.5 functionality <a href="https://github.com/erthalion/jsonbx">here</a>).
--- /dev/null
+CREATE EXTENSION jsonb_delete VERSION '1.0';
+-- jsonb deletion from an object should match on key/value
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+-- jsonb deletion from an array should only match on element
+SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+-- jsonb deletion from nested objects should not be part matched
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+ ?column?
+-------------------------
+ {"c": 3, "d": {"a": 4}}
+(1 row)
+
+-- but a match of all nested values should
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
+ ?column?
+------------------
+ {"a": 4, "c": 3}
+(1 row)
+
+SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
+ ?column?
+--------------------------------------------------------------------
+ {"a": {"b": {"c": [1, [2, 3, [4]], {"d": 1}]}, "c": [1, 2, 3, 4]}}
+(1 row)
+
+-- jsonb nulls are equal
+SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+-- others
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
+ ?column?
+------------------
+ {"a": 4, "c": 3}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
+ ?column?
+---------------
+ {"a": "test"}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+-------------------------------------------------------------------------
+ {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+------------------------------------------------------------
+ {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+---------------------------------------------------------------
+ {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+------------------------------------------------------
+ {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":true, "e":[1,2,3]}'::jsonb;
+ ?column?
+--------------------------------------------------------------
+ {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,"a",2]}'::jsonb;
+ ?column?
+-------------------------------------------------------------------------
+ {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,"a"]}'::jsonb;
+ ?column?
+-------------------------------------------------------
+ {"a": "test", "b": 2.2, "c": {"a": false}, "d": true}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+--------------------------------------------------
+ {"c": {"a": false}, "d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb;
+ ?column?
+-------------------------------
+ {"d": true, "e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,3]}'::jsonb;
+ ?column?
+--------------------
+ {"e": [1, 2, "a"]}
+(1 row)
+
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb;
+ ?column?
+----------
+ {}
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
+ ?column?
+----------------------------
+ ["a", 2, {"a": 1, "b": 2}]
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
+ ?column?
+----------------------------
+ ["a", 2, {"a": 1, "b": 2}]
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb;
+ ?column?
+----------
+ ["a", 2]
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb;
+ ?column?
+-----------------------
+ [2, {"a": 1, "b": 2}]
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb;
+ ?column?
+-------------------------
+ ["a", {"a": 1, "b": 2}]
+(1 row)
+
--- /dev/null
+\echo Use "CREATE EXTENSION jsonb_delete" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument';
+
+DROP OPERATOR IF EXISTS - (jsonb, jsonb);
+CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb);
+COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand';
+
--- /dev/null
+/*
+ * jsonb_delete.c
+ * Test jsonb delete operator functions for 9.4+
+ *
+ * Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ * Author: Glyn Astill <glyn@8kb.co.uk>
+ *
+ */
+
+#include "postgres.h"
+#include "fmgr.h"
+#include "utils/jsonb.h"
+#include "utils/builtins.h"
+#include "jsonb_delete.h"
+
+#ifdef PG_MODULE_MAGIC
+ PG_MODULE_MAGIC;
+#endif
+
+Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
+
+/*
+ * Operator function to delete top level keys and values from left operand
+ * where a match is found in the right operand.
+ *
+ * jsonb, jsonb -> jsonb
+ *
+ */
+Datum
+jsonb_delete_jsonb(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb1 = PG_GETARG_JSONB(0);
+ Jsonb *jb2 = PG_GETARG_JSONB(1);
+ JsonbValue *res = NULL;
+ JsonbParseState *state = NULL;
+ JsonbIterator *it, *it2;
+ JsonbValue v, v2;
+ JsonbValue key;
+ JsonbValue *lookup = NULL;
+ int32 r, r2;
+ bool push = true;
+
+ /* check if right jsonb is empty and return left if so */
+ if (JB_ROOT_COUNT(jb2) == 0)
+ PG_RETURN_JSONB(jb1);
+
+ it = JsonbIteratorInit(&jb1->root);
+
+ while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
+ {
+ push = true;
+
+ switch (r)
+ {
+ case WJB_BEGIN_ARRAY:
+ case WJB_BEGIN_OBJECT:
+ case WJB_END_ARRAY:
+ case WJB_END_OBJECT:
+ res = pushJsonbValue(&state, r, NULL);
+ break;
+ case WJB_ELEM:
+ if (v.type == jbvBinary)
+ {
+ it2 = JsonbIteratorInit(&jb2->root);
+ while ((r2 = JsonbIteratorNext(&it2, &v2, true)) != WJB_DONE)
+ {
+ if (v2.type == jbvBinary && v2.val.binary.len == v.val.binary.len &&
+ memcmp(v2.val.binary.data, v.val.binary.data, v2.val.binary.len) == 0)
+ {
+ push = false;
+ break;
+ }
+ }
+ }
+ else if (findJsonbValueFromContainer(&jb2->root, JB_FARRAY, &v) != NULL)
+ push = false;
+
+ if (push)
+ {
+ if (v.type == jbvBinary)
+ res = pushJsonbBinary(&state, v.val.binary.data);
+ else
+ res = pushJsonbValue(&state, WJB_ELEM, &v);
+ }
+ break;
+ case WJB_KEY:
+ lookup = findJsonbValueFromContainer(&jb2->root, JB_FOBJECT, &v);
+
+ key = v;
+ r = JsonbIteratorNext(&it, &v, true);
+
+ Assert(r == WJB_VALUE);
+
+ if (lookup != NULL && lookup->type == v.type)
+ {
+ switch (lookup->type)
+ {
+ /* Nulls within json are equal, but should not be equal to SQL nulls */
+ case jbvNull:
+ push = false;
+ break;
+ case jbvNumeric:
+ if (DatumGetBool(DirectFunctionCall2(numeric_eq,
+ PointerGetDatum(lookup->val.numeric),
+ PointerGetDatum(v.val.numeric))))
+ push = false;
+ break;
+ case jbvString:
+ if ((lookup->val.string.len == v.val.string.len) &&
+ (memcmp(lookup->val.string.val,
+ v.val.string.val,
+ lookup->val.string.len) == 0))
+ push = false;
+ break;
+ case jbvBinary:
+ if ((lookup->val.binary.len == v.val.binary.len) &&
+ (memcmp(lookup->val.binary.data,
+ v.val.binary.data,
+ lookup->val.binary.len) == 0))
+ push = false;
+ break;
+ case jbvBool:
+ if (lookup->val.boolean == v.val.boolean)
+ push = false;
+ break;
+ default:
+ ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unrecognized lookup type: %d", (int) lookup->type)));
+ /* inner switch end */
+ }
+ }
+
+ if (push)
+ {
+ res = pushJsonbValue(&state, WJB_KEY, &key);
+
+ /* if our value is nested binary data, iterate separately pushing each val */
+ if (v.type == jbvBinary)
+ res = pushJsonbBinary(&state, v.val.binary.data);
+ else
+ res = pushJsonbValue(&state, r, &v);
+ }
+ break;
+ case WJB_VALUE:
+ /* should not be possible */
+ default:
+ elog(ERROR, "impossible state: %d", r);
+ /* switch end */
+ }
+
+ }
+
+ if (JB_ROOT_IS_SCALAR(jb1) && !res->val.array.rawScalar && res->val.array.nElems == 1)
+ res->val.array.rawScalar = true;
+
+ PG_FREE_IF_COPY(jb1, 0);
+ PG_FREE_IF_COPY(jb2, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(res));
+}
+
--- /dev/null
+# jsonb_delete extension
+comment = 'hstore style delete function and operator for jsonb'
+default_version = '1.0'
+module_pathname = '$libdir/jsonb_delete'
+relocatable = true
--- /dev/null
+#ifndef __JSONB_DELETE_H__
+#define __JSONB_DELETE_H__
+
+extern JsonbValue * pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *in);
+
+#endif
--- /dev/null
+-- The function in this script is an SQL version for comparison
+-- of performance with C version.
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
--- /dev/null
+/*
+ * jsonb_delete_utils.c
+ * Test jsonb delete and operator functions for 9.4+
+ *
+ * Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ * Author: Glyn Astill <glyn@8kb.co.uk>
+ *
+ */
+
+#include "postgres.h"
+#include "utils/jsonb.h"
+#include "jsonb_delete.h"
+
+JsonbValue *
+pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *in)
+{
+ JsonbIterator *it;
+ JsonbValue v;
+ int32 r;
+ JsonbValue *res = NULL;
+
+ it = JsonbIteratorInit((void *)in);
+ while ((r = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
+ {
+ if (r == WJB_KEY || r == WJB_VALUE || r == WJB_ELEM)
+ res = pushJsonbValue(pstate, r, &v);
+ else
+ res = pushJsonbValue(pstate, r, NULL);
+
+ }
+ return res;
+}
--- /dev/null
+CREATE EXTENSION jsonb_delete VERSION '1.0';
+
+-- jsonb deletion from an object should match on key/value
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+
+-- jsonb deletion from an array should only match on element
+SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+
+-- jsonb deletion from nested objects should not be part matched
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+
+-- but a match of all nested values should
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
+SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{"d":2}'::jsonb;
+
+-- jsonb nulls are equal
+SELECT '{"a": 1, "b": 2, "c": null}'::jsonb - '{"a": 4, "c": null}'::jsonb;
+
+-- others
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":true, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,"a",2]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,"a"]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,3]}'::jsonb;
+SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb;
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb;
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb;
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb;
+
projects / postgresql / jsonb_delete / commitdiff