SELECT '"a"'::jsonb #- ARRAY['a'];
?column?
----------
-
+ []
(1 row)
SELECT '["a"]'::jsonb #- ARRAY['a'];
case WJB_BEGIN_ARRAY:
array_level++;
if (skip_key == 0)
- return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, NULL);
break;
case WJB_BEGIN_OBJECT:
nest_level++;
if (skip_key == 0)
- return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
break;
case WJB_ELEM:
/* only match array elements if they are text */
case WJB_BEGIN_ARRAY:
array_level++;
if (skip_key == 0)
- return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, NULL);
break;
case WJB_BEGIN_OBJECT:
nest_level++;
if (skip_key == 0)
- return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
break;
case WJB_ELEM:
/* only match array elements if they are text or null */
case WJB_BEGIN_OBJECT:
case WJB_END_ARRAY:
case WJB_END_OBJECT:
- return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, NULL);
break;
case WJB_ELEM:
/*
{
if (jsonb_iterator_value.type == jbvBinary)
{
- return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
}
else
{
/* if our value is nested binary data, iterate separately pushing each val */
if (jsonb_iterator_value.type == jbvBinary)
{
- return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
}
else
{
}
first = false;
- return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
}
first = true;
}
first = false;
- return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
}
return_jsonb_value = pushJsonbValue(&state, jsonb_root_close, NULL);
{
if ((jsonb_iterator_token == WJB_ELEM ) && (jsonb_iterator_value.type == jbvBinary))
{
- return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
}
else
{
- return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
}
+
Assert(jsonb_iterator_token != WJB_VALUE);
if ( jsonb_iterator_token == WJB_KEY )
jsonb_lookup_key.val.string.len = jsonb_iterator_value.val.string.len;
jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true);
- Assert(sonb_iterator_token == WJB_VALUE);
+ Assert(jsonb_iterator_token == WJB_VALUE);
jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root,
jsonb_lookup_flags, &jsonb_lookup_key);
/* if our value is nested binary data, iterate separately pushing each val */
if (jsonb_lookup_value->type == jbvBinary)
{
- return_jsonb_value = pushJsonbBinary(state, jsonb_lookup_value->val.binary.data);
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_lookup_value->val.binary.data);
}
else
{
#ifndef __JSONB_OPX_H__
#define __JSONB_OPX_H__
-extern JsonbValue * pushJsonbBinary(JsonbParseState *pstate, JsonbContainer *jsonb_container);
+extern JsonbValue * pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container);
+extern JsonbValue * pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_token, JsonbValue *jsonb_iterator_value);
extern Jsonb * jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b);
#endif
#include "jsonb_opx.h"
JsonbValue *
-pushJsonbBinary(JsonbParseState *pstate, JsonbContainer *jsonb_container)
+pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container)
{
JsonbIterator *jsonb_iterator;
JsonbValue jsonb_iterator_value;
jsonb_iterator = JsonbIteratorInit((void *)jsonb_container);
while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
{
- return_jsonb_value = pushJsonbValue(&pstate, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(pstate, jsonb_iterator_token, &jsonb_iterator_value);
}
return return_jsonb_value;
}
+JsonbValue *
+pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_token, JsonbValue *jsonb_iterator_value)
+{
+ JsonbValue *return_jsonb_value = NULL;
+
+ if ((jsonb_iterator_token == WJB_KEY) ||
+ (jsonb_iterator_token == WJB_VALUE) ||
+ (jsonb_iterator_token == WJB_ELEM) ||
+ (jsonb_iterator_token == WJB_BEGIN_ARRAY && jsonb_iterator_value->val.array.rawScalar))
+ {
+ return_jsonb_value = pushJsonbValue(pstate, jsonb_iterator_token, jsonb_iterator_value);
+ }
+ else
+ return_jsonb_value = pushJsonbValue(pstate, jsonb_iterator_token, NULL);
+
+ return return_jsonb_value;
+}
+
Jsonb *
jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b)
{
/* the current key we are looking for, starting with the first key */
text *key_on = NULL;
- //text *key_on;
int32 index_on = 0;
int32 nest_level = 0;
int32 array_level = 0;
while ((jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false)) != WJB_DONE)
{
if (((jsonb_last_token == jsonb_replacement_iterator_token) &&
- (jsonb_last_token != WJB_VALUE)) ||
+ (jsonb_last_token != WJB_VALUE)) ||
((jsonb_last_token == WJB_VALUE) &&
- ((jsonb_replacement_iterator_token == WJB_BEGIN_OBJECT) ||
- (jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY))))
+ ((jsonb_replacement_iterator_token == WJB_BEGIN_OBJECT) ||
+ (jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY))))
{
push_nest_level++;
}
if ((jsonb_replacement_iterator_token == WJB_KEY) ||
(jsonb_replacement_iterator_token == WJB_VALUE) ||
- (jsonb_replacement_iterator_token == WJB_ELEM) ||
- (push_nest_level != 1))
+ (jsonb_replacement_iterator_token == WJB_ELEM) || (push_nest_level != 1))
{
- return_jsonb_value = pushJsonbValue(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value);
}
if (((jsonb_last_token == WJB_BEGIN_ARRAY) ||
if (push && (skip_level == 0 || nest_level < skip_level))
{
- return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
jsonb_last_token = jsonb_iterator_token;
}
}
}
+ if (return_jsonb_value->type == jbvArray && return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 0)
+ {
+ return_jsonb_value->val.array.rawScalar = false;
+ }
+
return JsonbValueToJsonb(return_jsonb_value);
+
}
--- /dev/null
+MODULE_big = jsonb_opx
+DATA = jsonb_opx--1.0.sql jsonb_opx--1.1.sql jsonb_opx--1.0--1.1.sql
+OBJS = jsonb_opx.o jsonb_utilsx.o
+DOCS = README.md
+
+EXTENSION = jsonb_opx
+REGRESS = jsonb_opx
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
--- /dev/null
+jsonb_opx
+=========
+
+Missing operators for jsonb in PostgreSQL 9.4, this may contain some errors and bad form as it's primarily just experimentation (i'm not a frequent C programmer; but everyone has to start somewhere right?). Please test that it suits your requirements before using in any production scenario.
+
+Provides
+--------
+
+The following behave like hstore 1.x operators, i.e. without nested jsonb traversal
+
+* deletion using **-** operator
+ * jsonb_delete(jsonb, text)
+ * jsonb_delete(jsonb, text[])
+ * jsonb_delete(jsonb, jsonb)
+* concatenation using **||** operator
+ * jsonb_concat(jsonb, jsonb)
+* replacement using **=#** operator
+ * jsonb_replace(jsonb, jsonb)
+
+The following are intended to eventually function like hstore 2.0 operators
+
+* deletion at chained path using **#-** operator
+ jsonb_delete_path(jsonb, text[])
+* replacement at chained path using function
+ jsonb_replace_path(jsonb, text[], jsonb)
--- /dev/null
+CREATE EXTENSION jsonb_opx;
+-------------------------------------------------------------------------------
+-- Tests for jsonb - text
+-------------------------------------------------------------------------------
+-- text deletion from array containers will only delete string types as only strings can be keys:
+SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
+ ?column?
+-------------
+ [1, "1", 2]
+(1 row)
+
+-- simple text deletion from an object container
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
+ ?column?
+------------------
+ {"a": 1, "c": 3}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text;
+ ?column?
+-------------------------
+ {"a": 1, "c": {"b": 3}}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text;
+ ?column?
+---------------------------------
+ {"a": 1, "c": {"b": [1, 2, 3]}}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text;
+ ?column?
+--------------------------
+ {"a": 1, "c": [1, 2, 3]}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+-- simple text deletion from an object container should only match keys
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+-- others
+SELECT '["1", "2", true, false]'::jsonb - '2'::text;
+ ?column?
+--------------------
+ ["1", true, false]
+(1 row)
+
+SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
+ ?column?
+----------
+ ["1"]
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text;
+ ?column?
+-----------------------
+ [2, {"a": 1, "b": 2}]
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
+ ?column?
+----------
+ {"d": 2}
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text;
+ ?column?
+------------------------------------
+ {"a": {"b": 3, "c": [1, 2, 3, 4]}}
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text;
+ ?column?
+--------------------------------------------
+ {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}
+(1 row)
+
+-------------------------------------------------------------------------------
+-- Tests for jsonb - text[]
+-------------------------------------------------------------------------------
+-- text[] deletion from array containers will only delete string types as only strings can be keys:
+SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
+ ?column?
+----------
+ [1, 2]
+(1 row)
+
+-- simple text[] deletion from an object container
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
+ ?column?
+----------
+ {"c": 3}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c'];
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c'];
+ ?column?
+----------
+ {}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b'];
+ ?column?
+-----------------
+ {"c": {"b": 3}}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b'];
+ ?column?
+-------------------------
+ {"c": {"b": [1, 2, 3]}}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b'];
+ ?column?
+------------------
+ {"c": [1, 2, 3]}
+(1 row)
+
+SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
+ ?column?
+----------
+ {"b": 2}
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
+ ?column?
+------------------------------------
+ {"a": {"b": 3, "c": [1, 2, 3, 4]}}
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
+ ?column?
+----------
+ {"d": 2}
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
+ ?column?
+----------
+ {}
+(1 row)
+
+-- simple text[] deletion from an object container should only match keys or nulls
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
+ ?column?
+--------------------------
+ {"a": 1, "b": 2, "c": 3}
+(1 row)
+
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
+ ?column?
+-----------------------
+ [2, {"a": 1, "b": 2}]
+(1 row)
+
+SELECT '["1",2]'::jsonb - ARRAY[null];
+ ?column?
+----------
+ ["1", 2]
+(1 row)
+
+SELECT '["1",null,2]'::jsonb - ARRAY[null];
+ ?column?
+----------
+ ["1", 2]
+(1 row)
+
+-------------------------------------------------------------------------------
+-- Tests for jsonb - jsonb
+-------------------------------------------------------------------------------
+-- 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 key
+SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
+ ?column?
+----------
+ ["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 narcg
+SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
+ ?column?
+------------------
+ {"a": 4, "c": 3}
+(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)
+
+-- known issues !!!!
+-- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does not allow looking up non-scalar elements resulting in "invalid jsonb scalar type"
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
+ERROR: invalid jsonb scalar type
+SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
+ERROR: invalid jsonb scalar type
+-------------------------------------------------------------------------------
+-- Tests for jsonb || jsonb
+-------------------------------------------------------------------------------
+-- duplicates should automatically be removed by lower level logic
+SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
+ ?column?
+----------------------------------
+ {"a": 4, "b": 2, "c": 3, "d": 4}
+(1 row)
+
+-- concatentation of arrays
+SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+-- concatentation of scalars and arrays should be wrapped into arrays
+SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
+ ?column?
+-----------------
+ ["a", "b", "c"]
+(1 row)
+
+-- likewise concatentation of objects and arrays should be wrapped into arrays
+SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
+ ?column?
+------------------------------
+ ["a", "b", {"a": 4, "b": 2}]
+(1 row)
+
+-- and all concatentation should be in natural order supplied
+SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
+ ?column?
+----------------------------------------
+ [{"a": 4, "b": 2}, "a", "b", "c", "d"]
+(1 row)
+
+-- others
+SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
+ ?column?
+-----------------------------
+ [false, "a", "b", "c", "d"]
+(1 row)
+
+SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
+ ?column?
+--------------------------------------------------------
+ ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}]
+(1 row)
+
+SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
+ ?column?
+--------------------------------------------------------
+ [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"]
+(1 row)
+
+-------------------------------------------------------------------------------
+-- Tests for jsonb =# jsonb
+-------------------------------------------------------------------------------
+-- any keys existing in left argument have values replaced with those from righ argument
+SELECT '{"a": 1, "b": 2, "c":[1,2,3], "d":{"test":false}}'::jsonb #= '{"a": [1,2,3,4], "b": {"f":100, "j":{"k":200}}, "c": 4, "d":{"test":true}}'::jsonb;
+ ?column?
+------------------------------------------------------------------------------------
+ {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}}
+(1 row)
+
+-- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing
+SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb;
+ ?column?
+--------------------------------
+ {"a": [1, 2], "b": 2, "c": 12}
+(1 row)
+
+SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
+ ?column?
+--------------------------------
+ {"a": [1, 2], "b": 2, "c": 12}
+(1 row)
+
+SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
+ ?column?
+-----------
+ [1, 2, 3]
+(1 row)
+
+SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
+ ?column?
+----------
+ "a"
+(1 row)
+
+SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
+ ?column?
+------------------
+ {"a": 1, "b": 2}
+(1 row)
+
+-------------------------------------------------------------------------------
+-- Tests for jsonb #- text[]
+-------------------------------------------------------------------------------
+SELECT '"a"'::jsonb #- ARRAY['b'];
+ ?column?
+----------
+ "a"
+(1 row)
+
+SELECT '["a"]'::jsonb #- ARRAY['b'];
+ ?column?
+----------
+ ["a"]
+(1 row)
+
+SELECT '{"a":1}'::jsonb #- ARRAY['b'];
+ ?column?
+----------
+ {"a": 1}
+(1 row)
+
+SELECT '"a"'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ []
+(1 row)
+
+SELECT '["a"]'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ []
+(1 row)
+
+SELECT '{"a":1}'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ {}
+(1 row)
+
+SELECT '["a", "b"]'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ ["b"]
+(1 row)
+
+SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ {"b": 2}
+(1 row)
+
+SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ {"c": 2}
+(1 row)
+
+SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ {"b": 2}
+(1 row)
+
+SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b'];
+ ?column?
+---------------------
+ {"a": [1, 2, 3, 4]}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c'];
+ ?column?
+-------------------------------------
+ {"a": {"b": [1, 2, 3, ["a", "b"]]}}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a'];
+ ?column?
+----------
+ {"c": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c'];
+ ?column?
+---------------------------------------------
+ {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b'];
+ ?column?
+-------------------
+ {"a": {}, "c": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c'];
+ ?column?
+---------------------------------------------
+ {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2}
+(1 row)
+
+SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c'];
+ ?column?
+------------------------------------------------------
+ {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
+ ?column?
+------------------------------------
+ {"a": {"c": [1, 2, 3, 4]}, "d": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
+ ?column?
+---------------------------------------------
+ {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null];
+ ?column?
+----------------------------------------------------------------
+ {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2}
+(1 row)
+
+SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
+ ?column?
+------------------------------------
+ {"a": {"c": [1, 2, 3, 4]}, "d": 2}
+(1 row)
+
+SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d'];
+ ?column?
+-----------------
+ {"a": {"b": 3}}
+(1 row)
+
+SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
+ ?column?
+----------------------------------
+ [{"b": [1, 2, 3, 4, 5]}, 1, "c"]
+(1 row)
+
+SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
+ ?column?
+----------------------------------
+ ["a", {"b": [1, 2, 3, 4, 5]}, 1]
+(1 row)
+
+SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
+ ?column?
+------------------------------------
+ [1, [2, [3, [4, [5, 6, 7]]]], "a"]
+(1 row)
+
+SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
+ ?column?
+------------------------------------
+ [1, [2, [3, [4, [5, 6, 7]]]], "b"]
+(1 row)
+
+-- expected limitation: cannot call with path deeper than 1 on a non-object
+SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
+ERROR: cannot call with path deeper than 1 on a non-object
+-------------------------------------------------------------------------------
+-- Tests for jsonb_replace_path jsonb text[]
+-------------------------------------------------------------------------------
+SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------
+ {"b": 2, "f": 3}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------
+ {"f": 3}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------
+ {"a": {"f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+-------------------------
+ {"a": {"c": 1, "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+-------------------------
+ {"a": {"b": 1, "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------------------------
+ {"a": {"c": 2, "d": [1, 2], "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------------------------
+ {"a": {"b": 1, "d": [1, 2], "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+---------------------------------
+ {"a": {"b": 1, "c": 2, "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+--------------------
+ {"a": {"f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+-------------------------------------------------------
+ {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+-------------------------------------------------------
+ {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
+ jsonb_replace_path
+---------------------------------
+ {"a": {"b": 1, "c": 2, "f": 3}}
+(1 row)
+
+SELECT jsonb_replace_path('{"a":1, "b":null, "c":[1,2,null], "c":{"d":11}, "e":{"20":[100,"c"]}}', ARRAY['c'], '{"f":[[1],2], "g":"test", "h":{"i":{"j":null}}}');
+ jsonb_replace_path
+---------------------------------------------------------------------------------------------------
+ {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}}
+(1 row)
+
+SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
+ jsonb_replace_path
+--------------------
+ {"f": 10}
+(1 row)
+
+SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb);
+ jsonb_replace_path
+--------------------
+ "a"
+(1 row)
+
+SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
+ jsonb_replace_path
+--------------------
+ ["b", "a"]
+(1 row)
+
+SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb);
+ jsonb_replace_path
+--------------------
+ "5"
+(1 row)
+
--- /dev/null
+\echo Use "ALTER EXTENSION jsonb_opx UPDATE TO '1.1'" to load this file. \quit
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_delete_path(jsonb, text[])
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_path'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete_path(jsonb, text[]) IS 'follow path of keys in order supplied in array and delete end-point key value pair from jsonb';
+
+DROP OPERATOR IF EXISTS #- (jsonb, text[]);
+CREATE OPERATOR #- ( PROCEDURE = jsonb_delete_path, LEFTARG = jsonb, RIGHTARG = text[]);
+COMMENT ON OPERATOR #- (jsonb, text[]) IS 'delete key path from left operand';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_replace_path(jsonb, text[], jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_replace_path'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_replace_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and replace end-point key value pair with supplied jsonb';
--- /dev/null
+\echo Use "CREATE EXTENSION jsonb_opx" to load this file. \quit
+
+-- CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text)
+-- RETURNS jsonb
+-- AS 'SELECT jsonb_delete($1, ARRAY[$2]);'
+-- LANGUAGE SQL IMMUTABLE STRICT;
+-- COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
+
+CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_key'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
+
+DROP OPERATOR IF EXISTS - (jsonb, text);
+CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text);
+COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
+
+--
+CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, text[])
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_keys'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument';
+
+DROP OPERATOR IF EXISTS - (jsonb, text[]);
+CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]);
+COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
+
+--
+
+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';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_concat(jsonb, jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_concat_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments';
+
+DROP OPERATOR IF EXISTS || (jsonb, jsonb);
+CREATE OPERATOR || ( PROCEDURE = jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb);
+COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_replace(jsonb, jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_replace_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_replace(jsonb, jsonb) IS 'replace occurrences of second jsonb argument in first';
+
+DROP OPERATOR IF EXISTS #= (jsonb, jsonb);
+CREATE OPERATOR #= ( PROCEDURE = jsonb_replace, LEFTARG = jsonb, RIGHTARG = jsonb);
+COMMENT ON OPERATOR #= (jsonb, jsonb) IS 'replace values for matching keys in jsonb types';
--- /dev/null
+\echo Use "CREATE EXTENSION jsonb_opx" to load this file. \quit
+
+-- CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text)
+-- RETURNS jsonb
+-- AS 'SELECT jsonb_delete($1, ARRAY[$2]);'
+-- LANGUAGE SQL IMMUTABLE STRICT;
+-- COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
+
+CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_key'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument';
+
+DROP OPERATOR IF EXISTS - (jsonb, text);
+CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text);
+COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
+
+--
+CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, text[])
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_keys'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument';
+
+DROP OPERATOR IF EXISTS - (jsonb, text[]);
+CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]);
+COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
+
+--
+
+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';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_concat(jsonb, jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_concat_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments';
+
+DROP OPERATOR IF EXISTS || (jsonb, jsonb);
+CREATE OPERATOR || ( PROCEDURE = jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb);
+COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_replace(jsonb, jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_replace_jsonb'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_replace(jsonb, jsonb) IS 'replace occurrences of second jsonb argument in first';
+
+DROP OPERATOR IF EXISTS #= (jsonb, jsonb);
+CREATE OPERATOR #= ( PROCEDURE = jsonb_replace, LEFTARG = jsonb, RIGHTARG = jsonb);
+COMMENT ON OPERATOR #= (jsonb, jsonb) IS 'replace values for matching keys in jsonb types';
+
+-- 1.1 Extra functions start here
+
+CREATE OR REPLACE FUNCTION jsonb_delete_path(jsonb, text[])
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_delete_path'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_delete_path(jsonb, text[]) IS 'follow path of keys in order supplied in array and delete end-point key value pair from jsonb';
+
+DROP OPERATOR IF EXISTS #- (jsonb, text[]);
+CREATE OPERATOR #- ( PROCEDURE = jsonb_delete_path, LEFTARG = jsonb, RIGHTARG = text[]);
+COMMENT ON OPERATOR #- (jsonb, text[]) IS 'delete key path from left operand';
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_replace_path(jsonb, text[], jsonb)
+RETURNS jsonb
+ AS 'MODULE_PATHNAME', 'jsonb_replace_path'
+LANGUAGE C IMMUTABLE STRICT;
+COMMENT ON FUNCTION jsonb_replace_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and replace end-point key value pair with supplied jsonb';
+
--- /dev/null
+/*
+ * jsonb_opx.c
+ * Test jsonb delete and concatenate operator functions for 9.4
+ *
+ * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ * Author: Glyn Astill <glyn@8kb.co.uk>
+ *
+ * This is purely experimentation and will contain many errors and bad form
+ * DO NOT USE ON PRODUCTION SYSTEMS.
+ *
+ */
+
+#include "postgres.h"
+#include "fmgr.h"
+#include "utils/array.h"
+#include "utils/jsonb.h"
+#include "catalog/pg_type.h"
+#include "utils/builtins.h"
+#include "jsonb_opx.h"
+
+#ifdef PG_MODULE_MAGIC
+ PG_MODULE_MAGIC;
+#endif
+
+Datum jsonb_delete_key(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_delete_key);
+
+/*
+ * Operator function to delete key from left operand where a match is found in
+ * the right operand.
+ *
+ * jsonb, text -> jsonb
+ *
+ */
+Datum
+jsonb_delete_key(PG_FUNCTION_ARGS)
+{
+ /* pointers to incoming jsonb and text data */
+ Jsonb *input_jsonb = PG_GETARG_JSONB(0);
+ text *input_text = PG_GETARG_TEXT_P(1);
+
+ /* pointers to return jsonb value data and state to be converted to jsonb on return */
+ JsonbParseState *state = NULL;
+ JsonbValue *return_jsonb_value = NULL;
+
+ /* pointer to iterator for input_jsonb and lookup value data */
+ JsonbValue jsonb_lookup_key;
+ JsonbValue *jsonb_lookup_value = NULL;
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+
+ /* variables used for skip logic */
+ int32 skip_key = 0;
+ int32 nest_level = 0;
+ int32 array_level = 0;
+
+ /*
+ * if we are not deaing with an array first check to make sure the key exists
+ * this is potentially just extra unwanted work
+ */
+ if (!JB_ROOT_IS_ARRAY(input_jsonb))
+ {
+ jsonb_lookup_key.type = jbvString;
+ jsonb_lookup_key.val.string.val = VARDATA_ANY(input_text);
+ jsonb_lookup_key.val.string.len = VARSIZE_ANY_EXHDR(input_text);
+
+ jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb->root,
+ JB_FOBJECT | JB_FARRAY, &jsonb_lookup_key);
+
+ if (jsonb_lookup_value == NULL)
+ PG_RETURN_JSONB(input_jsonb);
+ }
+
+ /*
+ * If we've been supplied with an existing key iterate round json data and rebuild
+ * with key/element excluded.
+ *
+ * skip_key, nest_level and array_level are crude counts to check if the the value
+ * for the key is closed and ensure we don't match on keys within nested objects.
+ * Because we are recursing into nested elements but blindly just pushing them onto
+ * the return value we can get away without deeper knowledge of the json?
+ */
+
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
+ {
+ switch (jsonb_iterator_token)
+ {
+ case WJB_BEGIN_ARRAY:
+ array_level++;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, NULL);
+ break;
+ case WJB_BEGIN_OBJECT:
+ nest_level++;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
+ break;
+ case WJB_ELEM:
+ /* only match array elements if they are text */
+ if (skip_key == 0 && nest_level == 0 && array_level > 0)
+ {
+ if (jsonb_iterator_value.type == jbvString)
+ {
+ if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) &&
+ (memcmp(jsonb_iterator_value.val.string.val,
+ VARDATA_ANY(input_text),
+ jsonb_iterator_value.val.string.len) == 0))
+ break;
+ }
+ }
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
+ break;
+ case WJB_KEY:
+ /* Check each key against our array of keys */
+ if (skip_key > 0)
+ {
+ skip_key++;
+ }
+ else if (nest_level == 1 && array_level == 0)
+ {
+ if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) &&
+ (memcmp(jsonb_iterator_value.val.string.val,
+ VARDATA_ANY(input_text),
+ jsonb_iterator_value.val.string.len) == 0))
+ {
+ skip_key++;
+ break;
+ }
+ }
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
+ break;
+ case WJB_VALUE:
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
+ else if (skip_key > 0)
+ skip_key--;
+ break;
+ case WJB_END_ARRAY:
+ array_level--;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
+ else if (skip_key > 0 && array_level == 0)
+ skip_key--;
+ break;
+ case WJB_END_OBJECT:
+ nest_level--;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
+ else if (skip_key > 0)
+ skip_key--;
+ break;
+ default:
+ elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
+ }
+ }
+ PG_FREE_IF_COPY(input_jsonb, 0);
+ PG_FREE_IF_COPY(input_text, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
+}
+
+Datum jsonb_delete_keys(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_delete_keys);
+
+/*
+ * Operator function to delete keys from left operand where a match is found in
+ * the right operand.
+ *
+ * jsonb, text[] -> jsonb
+ *
+ */
+Datum
+jsonb_delete_keys(PG_FUNCTION_ARGS)
+{
+ /* general loops */
+ int i;
+
+ /* pointers to incoming jsonb and text[] data */
+ Jsonb *input_jsonb = PG_GETARG_JSONB(0);
+ ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
+
+ /* pointers to return jsonb value data and state to be converted to jsonb on return */
+ JsonbParseState *state = NULL;
+ JsonbValue *return_jsonb_value = NULL;
+
+ /* pointer to iterator for input_jsonb and lookup value data */
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+
+ /* variables used for skip logic */
+ int32 skip_key = 0;
+ int32 nest_level = 0;
+ int32 array_level = 0;
+
+ /* array element variables for use during deconstruction */
+ Datum *datums;
+ bool *nulls;
+ int32 count;
+
+ /* individual array values values from incoming text[] */
+ text *array_element_text;
+
+ /* assert input_array is a text array type */
+ Assert(ARR_ELEMTYPE(input_array) == TEXTOID);
+
+ /* check input_array is one-dimensional */
+ if (ARR_NDIM(input_array) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("1 dimensional text array expected")));
+
+ /* deconstruct array elements */
+ deconstruct_array(input_array, TEXTOID, -1, false, 'i',
+ &datums, &nulls, &count);
+
+ /* if the array is empty there's no work to do so return the input value */
+ if (count == 0)
+ PG_RETURN_JSONB(input_jsonb);
+
+ /*
+ * If we've been supplied with existing keys iterate round json data and rebuild
+ * with keys/elements excluded.
+ *
+ * skip_key, nest_level and array_level are crude counts to check if the the value
+ * for the key is closed and ensure we don't match on keys within nested objects.
+ * Because we are recursing into nested elements but blindly just pushing them onto
+ * the return value we can get away without deeper knowledge of the json?
+ */
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) {
+
+ switch (jsonb_iterator_token)
+ {
+ case WJB_BEGIN_ARRAY:
+ array_level++;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, NULL);
+ break;
+ case WJB_BEGIN_OBJECT:
+ nest_level++;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);
+ break;
+ case WJB_ELEM:
+ /* only match array elements if they are text or null */
+ if (skip_key == 0 && nest_level == 0 && array_level > 0)
+ {
+ if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull)
+ {
+ for (i=0; i<count; i++)
+ {
+ if (!nulls[i])
+ array_element_text = DatumGetTextP(datums[i]);
+ else
+ array_element_text = NULL;
+
+ if (((array_element_text != NULL) && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
+ (memcmp(jsonb_iterator_value.val.string.val,
+ VARDATA_ANY(array_element_text),
+ jsonb_iterator_value.val.string.len) == 0)) || ((array_element_text == NULL) && (jsonb_iterator_value.type == jbvNull)))
+ {
+ skip_key = 1;
+ break;
+ }
+ }
+ if (skip_key == 1)
+ {
+ skip_key = 0;
+ break;
+ }
+ }
+ }
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
+ break;
+ case WJB_KEY:
+ /* Check each key against our array of keys */
+ if (skip_key > 0)
+ {
+ skip_key++;
+ }
+ else if (nest_level == 1 && array_level == 0)
+ {
+ for (i=0; i<count; i++)
+ {
+ if (nulls[i])
+ continue;
+
+ array_element_text = DatumGetTextP(datums[i]);
+
+ if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
+ (memcmp(jsonb_iterator_value.val.string.val,
+ VARDATA_ANY(array_element_text),
+ jsonb_iterator_value.val.string.len) == 0))
+ {
+ skip_key++;
+ break;
+ }
+ }
+ }
+
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
+ break;
+ case WJB_VALUE:
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
+ else if (skip_key > 0)
+ skip_key--;
+ break;
+ case WJB_END_ARRAY:
+ array_level--;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
+ else if (skip_key > 0 && array_level == 0)
+ skip_key--;
+ break;
+ case WJB_END_OBJECT:
+ nest_level--;
+ if (skip_key == 0)
+ return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
+ else if (skip_key > 0)
+ skip_key--;
+ break;
+ default:
+ elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
+ }
+ }
+ PG_FREE_IF_COPY(input_jsonb, 0);
+ PG_FREE_IF_COPY(input_array, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
+}
+
+Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
+
+/*
+ * Operator function to delete 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)
+{
+ /* pointers to incoming jsonb and text[] data */
+ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
+ Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
+
+ /* pointers to return jsonb value data and state to be converted to jsonb on return */
+ JsonbValue *return_jsonb_value = NULL;
+ JsonbParseState *state = NULL;
+
+ /* pointer to iterator for input_jsonb_a and temporary value data */
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ JsonbValue jsonb_iterator_key;
+ int32 jsonb_iterator_token;
+ bool skip_nested = false;
+
+ bool push = true;
+
+ /* pointer to lookup on input_jsonb_b */
+ JsonbValue *jsonb_lookup_value = NULL;
+
+ /*
+ * check if either right jsonb is empty and return left if so
+ */
+ if (JB_ROOT_COUNT(input_jsonb_b) == 0)
+ PG_RETURN_JSONB(input_jsonb_a);
+
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE)
+ {
+ skip_nested = true;
+ push = true;
+
+ switch (jsonb_iterator_token)
+ {
+ case WJB_BEGIN_ARRAY:
+ case WJB_BEGIN_OBJECT:
+ case WJB_END_ARRAY:
+ case WJB_END_OBJECT:
+ return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, NULL);
+ break;
+ case WJB_ELEM:
+ /*
+ * findJsonbValueFromContainer only supports jsonb arrays containting scalar values?
+ * If container is something like '[[1]]' or '[{"a":1}]' will error with "invalid jsonb scalar type"
+ */
+ jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
+ if (jsonb_lookup_value == NULL)
+ {
+ if (jsonb_iterator_value.type == jbvBinary)
+ {
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
+ }
+ else
+ {
+ return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
+ }
+ }
+ break;
+ case WJB_KEY :
+ jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
+
+ jsonb_iterator_key = jsonb_iterator_value;
+ jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested);
+ if (jsonb_iterator_token != WJB_VALUE)
+ elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token);
+
+ if (jsonb_lookup_value != NULL)
+ {
+
+ if (jsonb_lookup_value->type == jsonb_iterator_value.type)
+ {
+ switch (jsonb_lookup_value->type)
+ {
+ case jbvNull:
+ push = false;
+ break;
+ case jbvNumeric:
+ if (DatumGetBool(DirectFunctionCall2(numeric_eq,
+ PointerGetDatum(jsonb_lookup_value->val.numeric),
+ PointerGetDatum(jsonb_iterator_value.val.numeric))))
+ push = false;
+ break;
+ case jbvString:
+ if ((jsonb_lookup_value->val.string.len == jsonb_iterator_value.val.string.len) &&
+ (memcmp(jsonb_lookup_value->val.string.val,
+ jsonb_iterator_value.val.string.val,
+ jsonb_lookup_value->val.string.len) == 0))
+ push = false;
+ break;
+ case jbvBinary:
+ if ((jsonb_lookup_value->val.binary.len == jsonb_iterator_value.val.binary.len) &&
+ (memcmp(jsonb_lookup_value->val.binary.data,
+ jsonb_iterator_value.val.binary.data,
+ jsonb_lookup_value->val.binary.len) == 0))
+ push = false;
+ break;
+ case jbvBool:
+ if (jsonb_lookup_value->val.boolean == jsonb_iterator_value.val.boolean)
+ push = false;
+ break;
+ case jbvArray:
+ /* should not be possible? */
+ case jbvObject:
+ /* should not be possible? */
+ default:
+ ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type %i", jsonb_iterator_token)));
+ }
+ }
+ }
+
+ if (push)
+ {
+ return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key);
+
+ /* if our value is nested binary data, iterate separately pushing each val */
+ if (jsonb_iterator_value.type == jbvBinary)
+ {
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
+ }
+ else
+ {
+ return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ }
+ }
+ break;
+ case WJB_VALUE:
+ /* should not be possible */
+ default:
+ elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
+ }
+
+ }
+
+ PG_FREE_IF_COPY(input_jsonb_a, 0);
+ PG_FREE_IF_COPY(input_jsonb_b, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
+}
+
+Datum jsonb_delete_path(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_delete_path);
+
+/*
+ * Test
+ * jsonb, text[] -> jsonb
+ *
+ */
+Datum
+jsonb_delete_path(PG_FUNCTION_ARGS)
+{
+ /* pointers to incoming jsonb and text[] data */
+ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
+ ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
+ //Jsonb *input_jsonb_b = PG_GETARG_JSONB(2);
+
+ /* pointer to return jsonb data */
+ Jsonb *return_jsonb = NULL;
+
+ return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, NULL);
+
+ PG_FREE_IF_COPY(input_jsonb_a, 0);
+ PG_FREE_IF_COPY(input_array, 1);
+ //PG_FREE_IF_COPY(input_jsonb_b, 2);
+
+ PG_RETURN_JSONB(return_jsonb);
+}
+
+Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_concat_jsonb);
+
+/*
+ * Operator function to concatenate json from left operand where a match
+ * is found in the right operand.
+ *
+ * jsonb, jsonb -> jsonb
+ *
+ */
+Datum
+jsonb_concat_jsonb(PG_FUNCTION_ARGS)
+{
+ /* incoming jsonb data */
+ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
+ Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
+
+ /* return jsonb value data to be converted to jsonb on return */
+ JsonbParseState *state = NULL;
+ JsonbValue *return_jsonb_value = NULL;
+
+ /* iterator for input_jsonb_b */
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+ int32 jsonb_root_open;
+ int32 jsonb_root_close;
+
+ int32 nest_level = 0;
+ bool first = true;
+
+ /*
+ * check if either supplied jsonb is empty and return the other if so
+ */
+ if (JB_ROOT_COUNT(input_jsonb_a) == 0)
+ PG_RETURN_JSONB(input_jsonb_b);
+ else if (JB_ROOT_COUNT(input_jsonb_b) == 0)
+ PG_RETURN_JSONB(input_jsonb_a);
+
+ /*
+ * rather than restrict concatenation to objects, allow any jsonb root
+ * but if one is an array use an array as the root container else
+ * default to object
+ */
+ if (JB_ROOT_IS_ARRAY(input_jsonb_a) || JB_ROOT_IS_ARRAY(input_jsonb_b))
+ {
+ jsonb_root_open = WJB_BEGIN_ARRAY;
+ jsonb_root_close = WJB_END_ARRAY;
+ } else
+ {
+ jsonb_root_open = WJB_BEGIN_OBJECT;
+ jsonb_root_close = WJB_END_OBJECT;
+ }
+
+ /*
+ * The following is essentially a cut 'n shut job; discarding the closing root
+ * object token from the first jsonb value and the opening one from the second.
+ * Values from each are just blindly pushed onto the return value leaving
+ * deduplication down to lower level jsonb logic.
+ */
+
+ return_jsonb_value = pushJsonbValue(&state, jsonb_root_open, NULL);
+
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
+ {
+ if (jsonb_iterator_token == jsonb_root_open && first)
+ {
+ nest_level++;
+ if (nest_level == 1)
+ continue;
+ }
+ else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0)
+ {
+ nest_level--;
+ if (nest_level == 0)
+ continue;
+ }
+ first = false;
+
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ }
+
+ first = true;
+ nest_level = 0;
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
+ {
+ if (jsonb_iterator_token == jsonb_root_open && first)
+ {
+ nest_level++;
+ if (nest_level == 1)
+ continue;
+ }
+ else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0)
+ {
+ nest_level--;
+ if (nest_level == 0)
+ continue;
+ }
+ first = false;
+
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ }
+
+ return_jsonb_value = pushJsonbValue(&state, jsonb_root_close, NULL);
+
+ PG_FREE_IF_COPY(input_jsonb_a, 0);
+ PG_FREE_IF_COPY(input_jsonb_b, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
+}
+
+Datum jsonb_replace_jsonb(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_replace_jsonb);
+
+/*
+ * Operator function to replace json in left operand where keys match
+ * in the right operand.
+ *
+ * jsonb, jsonb -> jsonb
+ *
+ */
+Datum
+jsonb_replace_jsonb(PG_FUNCTION_ARGS)
+{
+ /* incoming jsonb data */
+ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
+ Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
+
+ /* return jsonb value data to be converted to jsonb on return */
+ JsonbParseState *state = NULL;
+ JsonbValue *return_jsonb_value = NULL;
+
+ /* lookup jsonb value data */
+ JsonbValue jsonb_lookup_key;
+ JsonbValue *jsonb_lookup_value = NULL;
+ uint32 jsonb_lookup_flags;
+
+ /* iterator for input_jsonb_b */
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+
+ /*
+ * check if supplied replacement jsonb is empty and return unchanged if so
+ */
+ if (JB_ROOT_COUNT(input_jsonb_b) == 0)
+ PG_RETURN_JSONB(input_jsonb_a);
+
+ if (JB_ROOT_IS_OBJECT(input_jsonb_a))
+ jsonb_lookup_flags = JB_FOBJECT;
+ else
+ jsonb_lookup_flags = JB_FOBJECT | JB_FARRAY;
+
+ jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true)) != WJB_DONE)
+ {
+ if ((jsonb_iterator_token == WJB_ELEM ) && (jsonb_iterator_value.type == jbvBinary))
+ {
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
+ }
+ else
+ {
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ }
+
+ Assert(jsonb_iterator_token != WJB_VALUE);
+
+ if ( jsonb_iterator_token == WJB_KEY )
+ {
+ jsonb_lookup_key.type = jbvString;
+ jsonb_lookup_key.val.string.val = jsonb_iterator_value.val.string.val;
+ jsonb_lookup_key.val.string.len = jsonb_iterator_value.val.string.len;
+
+ jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true);
+ Assert(jsonb_iterator_token == WJB_VALUE);
+
+ jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root,
+ jsonb_lookup_flags, &jsonb_lookup_key);
+
+ /* if there's nothing to replace push the original value */
+ if (jsonb_lookup_value == NULL)
+ {
+ jsonb_lookup_value = &jsonb_iterator_value;
+ }
+
+ /* if our value is nested binary data, iterate separately pushing each val */
+ if (jsonb_lookup_value->type == jbvBinary)
+ {
+ return_jsonb_value = pushJsonbBinary(&state, jsonb_lookup_value->val.binary.data);
+ }
+ else
+ {
+ return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, jsonb_lookup_value);
+ }
+ }
+ }
+
+ PG_FREE_IF_COPY(input_jsonb_a, 0);
+ PG_FREE_IF_COPY(input_jsonb_b, 1);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
+}
+
+Datum jsonb_replace_path(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(jsonb_replace_path);
+
+/*
+ * Test
+ * jsonb, text[], jsonb -> jsonb
+ *
+ */
+Datum
+jsonb_replace_path(PG_FUNCTION_ARGS)
+{
+ /* pointers to incoming jsonb and text[] data */
+ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
+ ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
+ Jsonb *input_jsonb_b = PG_GETARG_JSONB(2);
+
+ /* pointer to return jsonb data */
+ Jsonb *return_jsonb = NULL;
+
+ return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, input_jsonb_b);
+
+ PG_FREE_IF_COPY(input_jsonb_a, 0);
+ PG_FREE_IF_COPY(input_array, 1);
+ PG_FREE_IF_COPY(input_jsonb_b, 2);
+
+ PG_RETURN_JSONB(return_jsonb);
+}
--- /dev/null
+# pg_opx extension
+comment = 'hstore style functions and operators for jsonb'
+default_version = '1.1'
+module_pathname = '$libdir/jsonb_opx'
+relocatable = true
--- /dev/null
+#ifndef __JSONB_OPX_H__
+#define __JSONB_OPX_H__
+
+extern JsonbValue * pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container);
+extern JsonbValue * pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_token, JsonbValue *jsonb_iterator_value);
+extern Jsonb * jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b);
+
+#endif
--- /dev/null
+-- The functions in this script are SQL versions of the C ones for comparison
+-- of performance between the two.
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text)
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE key <> b
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE key <> ALL(b)
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
+--
+
+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;
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_concat_left (a jsonb, b jsonb)
+RETURNS jsonb AS
+$BODY$
+SELECT json_object_agg(key, value)::jsonb FROM
+(
+ SELECT * FROM jsonb_each(a)
+ UNION ALL
+ SELECT * FROM jsonb_each(b)
+) a;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
--- /dev/null
+/*
+ * jsonb_utilsx.c
+ * Test jsonb delete and concatenate operator functions for 9.4
+ *
+ * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ * Author: Glyn Astill <glyn@8kb.co.uk>
+ *
+ * This is purely experimentation and will contain many errors and bad form
+ * DO NOT USE ON PRODUCTION SYSTEMS.
+ *
+ */
+
+#include "postgres.h"
+#include "utils/jsonb.h"
+#include "catalog/pg_type.h"
+#include "utils/builtins.h"
+#include "jsonb_opx.h"
+
+JsonbValue *
+pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container)
+{
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+ JsonbValue *return_jsonb_value = NULL;
+
+ jsonb_iterator = JsonbIteratorInit((void *)jsonb_container);
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
+ {
+ return_jsonb_value = pushJsonbValueBlind(pstate, jsonb_iterator_token, &jsonb_iterator_value);
+ }
+ return return_jsonb_value;
+}
+
+JsonbValue *
+pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_token, JsonbValue *jsonb_iterator_value)
+{
+ JsonbValue *return_jsonb_value = NULL;
+
+ if ((jsonb_iterator_token == WJB_KEY) ||
+ (jsonb_iterator_token == WJB_VALUE) ||
+ (jsonb_iterator_token == WJB_ELEM) ||
+ (jsonb_iterator_token == WJB_BEGIN_ARRAY && jsonb_iterator_value->val.array.rawScalar))
+ {
+ return_jsonb_value = pushJsonbValue(pstate, jsonb_iterator_token, jsonb_iterator_value);
+ }
+ else
+ return_jsonb_value = pushJsonbValue(pstate, jsonb_iterator_token, NULL);
+
+ return return_jsonb_value;
+}
+
+Jsonb *
+jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b)
+{
+ /* pointers to return jsonb value data and state to be converted to jsonb on return */
+ JsonbParseState *state = NULL;
+ JsonbValue *return_jsonb_value = NULL;
+
+ /* pointer to iterator for input jsonb */
+ JsonbIterator *jsonb_iterator;
+ JsonbValue jsonb_iterator_value;
+ int32 jsonb_iterator_token;
+ int32 jsonb_last_token = 0;
+
+ JsonbIterator *jsonb_replacement_iterator;
+ JsonbValue jsonb_replacement_iterator_value;
+ int32 jsonb_replacement_iterator_token;
+
+ /*
+ * array element variables for use during deconstruction
+ * count is the depth we will be looking from the first matching key
+ */
+ Datum *datums;
+ bool *nulls;
+ int32 count;
+
+ /* the current key we are looking for, starting with the first key */
+ text *key_on = NULL;
+ int32 index_on = 0;
+ int32 nest_level = 0;
+ int32 array_level = 0;
+ int32 skip_level = 0;
+ int32 push_nest_level = 0;
+ bool push = true;
+
+ /* assert input_array is a text array type */
+ Assert(ARR_ELEMTYPE(array_path) == TEXTOID);
+
+ /* check input_array is one-dimensional */
+ if (ARR_NDIM(array_path) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("1 dimensional text array expected")));
+
+ /* deconstruct array elements */
+ deconstruct_array(array_path, TEXTOID, -1, false, 'i',
+ &datums, &nulls, &count);
+
+ /* can't follow key based paths on non objects */
+ if (!JB_ROOT_IS_OBJECT(jsonb_a) && (count > 1))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot call with path deeper than 1 on a non-object")));
+
+ /* if the array is empty there's no work to do so return the input value */
+ if ((count == 0) || (JB_ROOT_COUNT(jsonb_a) == 0))
+ return jsonb_a;
+
+ if (!nulls[index_on])
+ key_on = DatumGetTextP(datums[index_on]);
+
+ jsonb_iterator = JsonbIteratorInit(&jsonb_a->root);
+
+ while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
+ {
+ push = true;
+
+ switch (jsonb_iterator_token)
+ {
+ case WJB_BEGIN_ARRAY:
+ array_level++;
+ break;
+ case WJB_BEGIN_OBJECT:
+ nest_level++;
+ break;
+ case WJB_ELEM:
+ case WJB_KEY:
+ /*
+ * If we are not skipping the current nest level check that the nesting level follows the array index
+ * and if it does check the current key. For array elements only check the root level (array_level==1).
+ * If there is no match we just keep on pushing
+ */
+ if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level-1 == index_on && array_level == 0) ||
+ (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1)))
+ {
+ if ((jsonb_iterator_value.type == jbvNull && key_on == NULL) ||
+ (key_on != NULL && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(key_on)) &&
+ (memcmp(jsonb_iterator_value.val.string.val, VARDATA_ANY(key_on), jsonb_iterator_value.val.string.len) == 0)))
+ {
+ /* if we have not yet reached the last index in the array / key chain move on and check the next */
+ if (index_on < count-1)
+ {
+ index_on++;
+ if (!nulls[index_on])
+ key_on = DatumGetTextP(datums[index_on]);
+ else
+ key_on = NULL;
+ }
+ /* if we have reached the last index, the we can modify this level */
+ else
+ {
+ /* if jsonb_b is not null unwrap it with iterator into replacement_jsonb_value */
+ if (jsonb_b != NULL) {
+ jsonb_replacement_iterator = JsonbIteratorInit(&jsonb_b->root);
+
+ while ((jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false)) != WJB_DONE)
+ {
+ if (((jsonb_last_token == jsonb_replacement_iterator_token) &&
+ (jsonb_last_token != WJB_VALUE)) ||
+ ((jsonb_last_token == WJB_VALUE) &&
+ ((jsonb_replacement_iterator_token == WJB_BEGIN_OBJECT) ||
+ (jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY))))
+ {
+ push_nest_level++;
+ }
+
+ if ((jsonb_replacement_iterator_token == WJB_KEY) ||
+ (jsonb_replacement_iterator_token == WJB_VALUE) ||
+ (jsonb_replacement_iterator_token == WJB_ELEM) || (push_nest_level != 1))
+ {
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value);
+ }
+
+ if (((jsonb_last_token == WJB_BEGIN_ARRAY) ||
+ (jsonb_last_token == WJB_VALUE)) &&
+ (jsonb_replacement_iterator_token == WJB_END_ARRAY))
+ {
+ push_nest_level--;
+ }
+ else if (((jsonb_last_token == WJB_BEGIN_OBJECT) ||
+ (jsonb_last_token == WJB_VALUE)) &&
+ (jsonb_replacement_iterator_token == WJB_END_OBJECT))
+ {
+ push_nest_level--;
+ }
+ }
+ }
+ if (jsonb_iterator_token == WJB_ELEM)
+ push = false;
+ else
+ skip_level = nest_level;
+ }
+ }
+ }
+ }
+
+ if (push && (skip_level == 0 || nest_level < skip_level))
+ {
+ return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
+ jsonb_last_token = jsonb_iterator_token;
+ }
+
+ switch (jsonb_iterator_token)
+ {
+ case WJB_END_OBJECT:
+ nest_level--;
+ if (skip_level == nest_level && array_level == 0)
+ skip_level = 0;
+ break;
+ case WJB_END_ARRAY:
+ array_level--;
+ if (skip_level == nest_level && array_level == 0)
+ skip_level = 0;
+ break;
+ case WJB_VALUE:
+ if (skip_level == nest_level)
+ skip_level = 0;
+ }
+ }
+
+ if (return_jsonb_value->type == jbvArray && return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 0)
+ {
+ return_jsonb_value->val.array.rawScalar = false;
+ }
+
+ return JsonbValueToJsonb(return_jsonb_value);
+
+}
projects / postgresql / pg_jsonb_opx / commitdiff