From b2272ef16fe0e6aa9b769f4517e9fe7177e3f444 Mon Sep 17 00:00:00 2001 From: glyn Date: Tue, 24 Feb 2015 20:42:20 +0000 Subject: [PATCH] Fix some logic when modifying scalars and simplify simple text deletion. Also remove bunch of files comitted in error. --- README.md | 4 +- expected/jsonb_opx.out | 266 ++++++++++- jsonb_opx.c | 240 ++++------ jsonb_utilsx.c | 68 +-- sql/Makefile | 11 - sql/README.md | 25 - sql/expected/jsonb_opx.out | 669 --------------------------- sql/jsonb_opx--1.0--1.1.sql | 21 - sql/jsonb_opx--1.0.sql | 64 --- sql/jsonb_opx--1.1.sql | 85 ---- sql/jsonb_opx.c | 764 ------------------------------- sql/jsonb_opx.control | 5 - sql/jsonb_opx.h | 8 - sql/jsonb_opx.sql | 77 +++- sql/jsonb_opx_sql_comparison.sql | 59 --- sql/jsonb_utilsx.c | 230 ---------- 16 files changed, 459 insertions(+), 2137 deletions(-) delete mode 100755 sql/Makefile delete mode 100755 sql/README.md delete mode 100755 sql/expected/jsonb_opx.out delete mode 100755 sql/jsonb_opx--1.0--1.1.sql delete mode 100755 sql/jsonb_opx--1.0.sql delete mode 100755 sql/jsonb_opx--1.1.sql delete mode 100755 sql/jsonb_opx.c delete mode 100755 sql/jsonb_opx.control delete mode 100755 sql/jsonb_opx.h delete mode 100755 sql/jsonb_opx_sql_comparison.sql delete mode 100755 sql/jsonb_utilsx.c diff --git a/README.md b/README.md index 0575f0d..7fa1a43 100755 --- a/README.md +++ b/README.md @@ -14,12 +14,12 @@ The following behave like hstore 1.x operators, i.e. without nested jsonb traver * jsonb_delete(jsonb, jsonb) * concatenation using **||** operator * jsonb_concat(jsonb, jsonb) -* replacement using **=#** operator +* 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 +* replacement at chained path using function (no operator) jsonb_replace_path(jsonb, text[], jsonb) diff --git a/expected/jsonb_opx.out b/expected/jsonb_opx.out index d7cd3c9..e11187b 100755 --- a/expected/jsonb_opx.out +++ b/expected/jsonb_opx.out @@ -2,7 +2,43 @@ 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 '["a", "b"]'::jsonb - 'b'::text; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a"]'::jsonb - 'b'::text; + ?column? +---------- + ["a"] +(1 row) + +SELECT '"a"'::jsonb - 'b'::text; + ?column? +---------- + "a" +(1 row) + +SELECT '[1]'::jsonb - 'b'::text; + ?column? +---------- + [1] +(1 row) + +SELECT '1'::jsonb - 'b'::text; + ?column? +---------- + 1 +(1 row) + +SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text; + ?column? +----------------- + ["a", {"a": 1}] +(1 row) + +-- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; ?column? ------------- @@ -53,6 +89,26 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; {"a": 1, "b": 2, "c": 3} (1 row) +-- deleting everything always results in an empty container +SELECT '["a"]'::jsonb - 'a'::text; + ?column? +---------- + [] +(1 row) + +SELECT '{"a":1}'::jsonb - 'a'::text; + ?column? +---------- + {} +(1 row) + +-- even for scalars, but this should perhaps error +SELECT '"a"'::jsonb - 'a'::text; + ?column? +---------- + [] +(1 row) + -- others SELECT '["1", "2", true, false]'::jsonb - '2'::text; ?column? @@ -90,10 +146,16 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} (1 row) +SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; + ?column? +---------- + {"d": 2} +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- --- text[] deletion from array containers will only delete string types as only strings can be keys: +-- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; ?column? ---------- @@ -161,6 +223,30 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; {} (1 row) +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; + ?column? +---------- + {} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['a']; + ?column? +---------------------------------------------------- + {"d": [2, [2], [1, 2, {"a": 2}, {"b": [1, [2]]}]]} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['d']; + ?column? +------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}} +(1 row) + +SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[]; + ?column? +---------- + {"d": 2} +(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? @@ -174,13 +260,14 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; [2, {"a": 1, "b": 2}] (1 row) -SELECT '["1",2]'::jsonb - ARRAY[null]; +-- here we treat SQL nulls and json nulls as equal - bad? +SELECT '["1",null,2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] (1 row) -SELECT '["1",null,2]'::jsonb - ARRAY[null]; +SELECT '["1",2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] @@ -210,13 +297,26 @@ SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::js {"c": 3, "d": {"a": 4}} (1 row) --- but a match of all nested values should narcg +-- 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? @@ -297,7 +397,8 @@ SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::js (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" +-- 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; @@ -312,6 +413,12 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb; {"a": 4, "b": 2, "c": 3, "d": 4} (1 row) +SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb; + ?column? +------------------------------------- + {"a": 4, "b": null, "c": 3, "d": 4} +(1 row) + -- concatentation of arrays SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb; ?column? @@ -359,17 +466,55 @@ SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"] (1 row) +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; + ?column? +---------------------------------------------------------------------------------------------------------------------------------------- + [{"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2}, "a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}] +(1 row) + +SELECT'["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; + ?column? +------------------------------------------------------------------------------------------------------------------------------------ + ["a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}, "a", ["b", "c", ["3", 1, 2, [9, 3, {"s": "o"}, "x"]]], {"f": 9}] +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb; + ?column? +--------------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} +(1 row) + ------------------------------------------------------------------------------- --- Tests for jsonb =# jsonb +-- Tests for jsonb #= jsonb ------------------------------------------------------------------------------- --- any keys existing in left argument have values replaced with those from righ argument +-- any keys existing in left argument have values replaced with those from righ +-- argument +SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4]}'::jsonb; + ?column? +--------------------- + {"a": [1, 2, 3, 4]} +(1 row) + +SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; + ?column? +--------------------- + {"a": [1, 2, 3, 4]} +(1 row) + +SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; + ?column? +----------------------------- + {"a": [1, 2, 3, 4], "b": 2} +(1 row) + 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 +-- 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? -------------------------------- @@ -400,6 +545,12 @@ SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; {"a": 1, "b": 2} (1 row) +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #= '{"a":{"b":3, "c":[1,{"r":[true,{"u":2}]},3,4,5]}}'::jsonb; + ?column? +--------------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [true, {"u": 2}]}, 3, 4, 5]}, "d": 2} +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb #- text[] ------------------------------------------------------------------------------- @@ -559,12 +710,95 @@ SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; [1, [2, [3, [4, [5, 6, 7]]]], "b"] (1 row) +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; + ?column? +------------------------------------------------------------- + {"a": {"c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; + ?column? +------------------------- + {"a": {"b": 3}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d']; + ?column? +--------------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d']; + ?column? +------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r']; + ?column? +--------------------------------------------------------------------- + {"a": {"b": 3, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r']; + ?column? +---------------------------------- + {"a": {"b": 3, "c": {}}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u']; + ?column? +----------------------------------------- + {"a": {"b": 3, "c": {"r": {}}}, "d": 2} +(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[] ------------------------------------------------------------------------------- +-- if the replacement on an object/array is passed as a scalar/array the value/element +-- is replaced +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '3'::jsonb); + jsonb_replace_path +-------------------- + {"a": 3, "b": 2} +(1 row) + +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb); + jsonb_replace_path +-------------------- + {"a": [3], "b": 2} +(1 row) + +SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb); + jsonb_replace_path +-------------------- + [3, "b"] +(1 row) + +SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb); + jsonb_replace_path +-------------------- + [3, "b"] +(1 row) + +-- if the replacement on an object/array is passed as an object the whole key-value +-- pair is replaced. This difference is perhaps confusing, but otherwise there is +-- no way to directly replace a key without deletion and concatenation. +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"z":3}'::jsonb); + jsonb_replace_path +-------------------- + {"b": 2, "z": 3} +(1 row) + +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb); + jsonb_replace_path +------------------------- + {"a": {"z": 3}, "b": 2} +(1 row) + SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb); jsonb_replace_path -------------------- @@ -664,6 +898,18 @@ SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); jsonb_replace_path -------------------- - "5" + [1, 2, 3, "5"] +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb, ARRAY['a','c','r','u'],'{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); + jsonb_replace_path +---------------------------------------------------------------------------- + {"a": {"b": 3, "c": {"r": {"a": {"b": 3, "c": {"r": {"u": 1}}}}}}, "d": 2} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb, ARRAY['a','b'], '{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); + jsonb_replace_path +-------------------------------------------------------------------------------------------------- + {"a": {"a": {"b": 3, "c": {"r": {"u": 1}}}, "c": [1, {"r": [null, {"u": 1}]}, 2, 3, 4]}, "d": 2} (1 row) diff --git a/jsonb_opx.c b/jsonb_opx.c index 377d609..29ea74a 100755 --- a/jsonb_opx.c +++ b/jsonb_opx.c @@ -45,34 +45,16 @@ jsonb_delete_key(PG_FUNCTION_ARGS) 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; + /* pointer to iterator for input_jsonb value data */ JsonbIterator *jsonb_iterator; JsonbValue jsonb_iterator_value; int32 jsonb_iterator_token; /* variables used for skip logic */ - int32 skip_key = 0; + int32 skip_level = 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); - } + bool push = true; /* * If we've been supplied with an existing key iterate round json data and rebuild @@ -88,78 +70,63 @@ jsonb_delete_key(PG_FUNCTION_ARGS) 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++; - 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) + case WJB_KEY: + if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level == 1 && array_level == 0) || + (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1))) { - if (jsonb_iterator_value.type == jbvString) + 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 (jsonb_iterator_token == WJB_ELEM) + push = false; + else + skip_level = nest_level; + } } } - 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--; + } + + if (push && (skip_level == 0 || nest_level < skip_level)) + { + return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value); + } + + 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_key == 0) - return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL); - else if (skip_key > 0 && array_level == 0) - skip_key--; + if (skip_level == nest_level && array_level == 0) + skip_level = 0; 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); + case WJB_VALUE: + if (skip_level == nest_level) + skip_level = 0; } } + + if (JB_ROOT_IS_SCALAR(input_jsonb) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1) + return_jsonb_value->val.array.rawScalar = true; + PG_FREE_IF_COPY(input_jsonb, 0); PG_FREE_IF_COPY(input_text, 1); @@ -191,15 +158,16 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) JsonbParseState *state = NULL; JsonbValue *return_jsonb_value = NULL; - /* pointer to iterator for input_jsonb and lookup value data */ + /* pointer to iterator for input_jsonb value data */ JsonbIterator *jsonb_iterator; JsonbValue jsonb_iterator_value; int32 jsonb_iterator_token; /* variables used for skip logic */ - int32 skip_key = 0; + int32 skip_level = 0; int32 nest_level = 0; int32 array_level = 0; + bool push = true; /* array element variables for use during deconstruction */ Datum *datums; @@ -230,7 +198,7 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) * 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 + * skip_level, 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? @@ -239,29 +207,27 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) 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++; - 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) + case WJB_KEY: + if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level == 1 && array_level == 0) || + (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1))) { - if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull) + if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull) { for (i=0; i 0) - { - skip_key++; - } - else if (nest_level == 1 && array_level == 0) - { - for (i=0; i 0) - skip_key--; + 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_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--; + if (skip_level == nest_level && array_level == 0) + skip_level = 0; break; - default: - elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token); + case WJB_VALUE: + if (skip_level == nest_level) + skip_level = 0; } } + + if (JB_ROOT_IS_SCALAR(input_jsonb) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1) + return_jsonb_value->val.array.rawScalar = true; + PG_FREE_IF_COPY(input_jsonb, 0); PG_FREE_IF_COPY(input_array, 1); @@ -490,6 +426,9 @@ jsonb_delete_jsonb(PG_FUNCTION_ARGS) } + if (JB_ROOT_IS_SCALAR(input_jsonb_a) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1) + return_jsonb_value->val.array.rawScalar = true; + PG_FREE_IF_COPY(input_jsonb_a, 0); PG_FREE_IF_COPY(input_jsonb_b, 1); @@ -511,7 +450,6 @@ 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; @@ -520,7 +458,6 @@ jsonb_delete_path(PG_FUNCTION_ARGS) 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); } @@ -555,8 +492,8 @@ jsonb_concat_jsonb(PG_FUNCTION_ARGS) int32 jsonb_root_close; int32 nest_level = 0; - bool first = true; - + bool first = true; + /* * check if either supplied jsonb is empty and return the other if so */ @@ -593,43 +530,49 @@ jsonb_concat_jsonb(PG_FUNCTION_ARGS) while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) { - if (jsonb_iterator_token == jsonb_root_open && first) + if (jsonb_iterator_token == jsonb_root_open && (first || nest_level > 0)) { nest_level++; - if (nest_level == 1) + if (nest_level == 1) + { + first = false; continue; + } } - else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0) + else if (jsonb_iterator_token == jsonb_root_close && nest_level > 0) { nest_level--; if (nest_level == 0) continue; } - first = false; + first = false; return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value); } - first = true; nest_level = 0; + first = true; 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) + if (jsonb_iterator_token == jsonb_root_open && (first || nest_level > 0)) { nest_level++; if (nest_level == 1) + { + first = false; continue; + } } - else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0) + else if (jsonb_iterator_token == jsonb_root_close && nest_level > 0) { nest_level--; if (nest_level == 0) continue; } - first = false; + first = false; return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value); } @@ -728,6 +671,9 @@ jsonb_replace_jsonb(PG_FUNCTION_ARGS) } } + if (JB_ROOT_IS_SCALAR(input_jsonb_a) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1) + return_jsonb_value->val.array.rawScalar = true; + PG_FREE_IF_COPY(input_jsonb_a, 0); PG_FREE_IF_COPY(input_jsonb_b, 1); diff --git a/jsonb_utilsx.c b/jsonb_utilsx.c index a08829c..f7a3de9 100755 --- a/jsonb_utilsx.c +++ b/jsonb_utilsx.c @@ -40,8 +40,7 @@ pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_ 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)) + (jsonb_iterator_token == WJB_ELEM)) { return_jsonb_value = pushJsonbValue(pstate, jsonb_iterator_token, jsonb_iterator_value); } @@ -62,7 +61,6 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) 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; @@ -155,35 +153,45 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) 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 the replacement value is a scalar then it will replace the current element or value */ + if (JB_ROOT_IS_SCALAR(jsonb_b)) { - 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)))) + jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false); + jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false); + + if (jsonb_iterator_token == WJB_ELEM) { - push_nest_level++; + return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_replacement_iterator_value); } - - if ((jsonb_replacement_iterator_token == WJB_KEY) || - (jsonb_replacement_iterator_token == WJB_VALUE) || - (jsonb_replacement_iterator_token == WJB_ELEM) || (push_nest_level != 1)) + else { - return_jsonb_value = pushJsonbValueBlind(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value); + return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value); + return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &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)) + } + /* otherwise assume this is the replacement for the whole element /key-value pair */ + else { + while ((jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false)) != WJB_DONE) { - push_nest_level--; + if (push_nest_level == 0 && jsonb_iterator_token == WJB_KEY && jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY) + { + return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value); + } + + if ((jsonb_replacement_iterator_token == WJB_BEGIN_OBJECT) || (jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY)) + push_nest_level++; + + if ((push_nest_level > 1) || + (jsonb_iterator_token == WJB_ELEM && jsonb_replacement_iterator_token != WJB_BEGIN_ARRAY && jsonb_replacement_iterator_token != WJB_END_ARRAY) || + (jsonb_iterator_token == WJB_KEY && jsonb_replacement_iterator_token != WJB_BEGIN_OBJECT && jsonb_replacement_iterator_token != WJB_END_OBJECT)) + { + return_jsonb_value = pushJsonbValueBlind(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value); + } + + if ((jsonb_replacement_iterator_token == WJB_END_OBJECT || jsonb_replacement_iterator_token == WJB_END_ARRAY)) + push_nest_level--; + + Assert(push_nest_level >= 0); } } } @@ -199,7 +207,6 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) 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) @@ -220,9 +227,12 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) } } - if (return_jsonb_value->type == jbvArray && return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 0) + if (return_jsonb_value->type == jbvArray) { - return_jsonb_value->val.array.rawScalar = false; + if (return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 0) + return_jsonb_value->val.array.rawScalar = false; + else if (JB_ROOT_IS_SCALAR(jsonb_a) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1) + return_jsonb_value->val.array.rawScalar = true; } return JsonbValueToJsonb(return_jsonb_value); diff --git a/sql/Makefile b/sql/Makefile deleted file mode 100755 index ef5486d..0000000 --- a/sql/Makefile +++ /dev/null @@ -1,11 +0,0 @@ -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) diff --git a/sql/README.md b/sql/README.md deleted file mode 100755 index 0575f0d..0000000 --- a/sql/README.md +++ /dev/null @@ -1,25 +0,0 @@ -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) diff --git a/sql/expected/jsonb_opx.out b/sql/expected/jsonb_opx.out deleted file mode 100755 index d7cd3c9..0000000 --- a/sql/expected/jsonb_opx.out +++ /dev/null @@ -1,669 +0,0 @@ -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) - diff --git a/sql/jsonb_opx--1.0--1.1.sql b/sql/jsonb_opx--1.0--1.1.sql deleted file mode 100755 index a1eb5ad..0000000 --- a/sql/jsonb_opx--1.0--1.1.sql +++ /dev/null @@ -1,21 +0,0 @@ -\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'; diff --git a/sql/jsonb_opx--1.0.sql b/sql/jsonb_opx--1.0.sql deleted file mode 100755 index 5a85278..0000000 --- a/sql/jsonb_opx--1.0.sql +++ /dev/null @@ -1,64 +0,0 @@ -\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'; diff --git a/sql/jsonb_opx--1.1.sql b/sql/jsonb_opx--1.1.sql deleted file mode 100755 index 95e0807..0000000 --- a/sql/jsonb_opx--1.1.sql +++ /dev/null @@ -1,85 +0,0 @@ -\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'; - diff --git a/sql/jsonb_opx.c b/sql/jsonb_opx.c deleted file mode 100755 index 377d609..0000000 --- a/sql/jsonb_opx.c +++ /dev/null @@ -1,764 +0,0 @@ -/* - * 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 - * - * 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 0) - { - skip_key++; - } - else if (nest_level == 1 && array_level == 0) - { - for (i=0; i 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); -} diff --git a/sql/jsonb_opx.control b/sql/jsonb_opx.control deleted file mode 100755 index c1355bf..0000000 --- a/sql/jsonb_opx.control +++ /dev/null @@ -1,5 +0,0 @@ -# pg_opx extension -comment = 'hstore style functions and operators for jsonb' -default_version = '1.1' -module_pathname = '$libdir/jsonb_opx' -relocatable = true diff --git a/sql/jsonb_opx.h b/sql/jsonb_opx.h deleted file mode 100755 index 730eec1..0000000 --- a/sql/jsonb_opx.h +++ /dev/null @@ -1,8 +0,0 @@ -#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 diff --git a/sql/jsonb_opx.sql b/sql/jsonb_opx.sql index a270fe9..7ad02b4 100755 --- a/sql/jsonb_opx.sql +++ b/sql/jsonb_opx.sql @@ -4,7 +4,14 @@ 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 '["a", "b"]'::jsonb - 'b'::text; +SELECT '["a"]'::jsonb - 'b'::text; +SELECT '"a"'::jsonb - 'b'::text; +SELECT '[1]'::jsonb - 'b'::text; +SELECT '1'::jsonb - 'b'::text; +SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text; + +-- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; -- simple text deletion from an object container @@ -18,6 +25,13 @@ SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text; -- simple text deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; +-- deleting everything always results in an empty container +SELECT '["a"]'::jsonb - 'a'::text; +SELECT '{"a":1}'::jsonb - 'a'::text; + +-- even for scalars, but this should perhaps error +SELECT '"a"'::jsonb - 'a'::text; + -- others SELECT '["1", "2", true, false]'::jsonb - '2'::text; SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text; @@ -25,12 +39,13 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; +SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- --- text[] deletion from array containers will only delete string types as only strings can be keys: +-- text deletion from array containers will only delete string types currently SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; -- simple text[] deletion from an object container @@ -44,12 +59,18 @@ SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c']; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d']; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a']; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['a']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2},{"b":[1,[2]]}]]}'::jsonb - ARRAY['d']; +SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - '{a}'::text[]; -- simple text[] deletion from an object container should only match keys or nulls SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; -SELECT '["1",2]'::jsonb - ARRAY[null]; + +-- here we treat SQL nulls and json nulls as equal - bad? SELECT '["1",null,2]'::jsonb - ARRAY[null]; +SELECT '["1",2]'::jsonb - ARRAY[null]; ------------------------------------------------------------------------------- -- Tests for jsonb - jsonb @@ -64,8 +85,12 @@ 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 narcg +-- 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; @@ -83,7 +108,8 @@ SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::js 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; -- 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" +-- 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; SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; @@ -93,6 +119,7 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::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; +SELECT '{"a": 1, "b": null, "c": 3}'::jsonb || '{"a": 4, "b": null, "d": 4}'::jsonb; -- concatentation of arrays SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb; @@ -110,20 +137,31 @@ SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb; SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; +SELECT'["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb || '["a",["b","c",["3",1,2,[9,3,{"s":"o"},"x"]]],{"f":9}]'::jsonb; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb || '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb; ------------------------------------------------------------------------------- --- Tests for jsonb =# jsonb +-- Tests for jsonb #= jsonb ------------------------------------------------------------------------------- --- any keys existing in left argument have values replaced with those from righ argument +-- any keys existing in left argument have values replaced with those from righ +-- argument +SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4]}'::jsonb; +SELECT '{"a": 1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; +SELECT '{"a": 1, "b":1}'::jsonb #= '{"a": [1,2,3,4], "b":2}'::jsonb; + 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; --- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing +-- 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; SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb; SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb; SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb; SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #= '{"a":{"b":3, "c":[1,{"r":[true,{"u":2}]},3,4,5]}}'::jsonb; + ------------------------------------------------------------------------------- -- Tests for jsonb #- text[] @@ -162,6 +200,13 @@ SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a']; SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c']; SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b']; SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','d']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['d']; +SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c','r']; +SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r']; +SELECT '{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb #- ARRAY['a','c','r','u']; -- expected limitation: cannot call with path deeper than 1 on a non-object SELECT '["a", "b"]'::jsonb #- ARRAY['a','b']; @@ -169,6 +214,19 @@ SELECT '["a", "b"]'::jsonb #- ARRAY['a','b']; ------------------------------------------------------------------------------- -- Tests for jsonb_replace_path jsonb text[] ------------------------------------------------------------------------------- + +-- if the replacement on an object/array is passed as a scalar/array the value/element +-- is replaced +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '3'::jsonb); +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '[3]'::jsonb); +SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '3'::jsonb); +SELECT jsonb_replace_path('["a", "b"]', ARRAY['a'], '[3]'::jsonb); +-- if the replacement on an object/array is passed as an object the whole key-value +-- pair is replaced. This difference is perhaps confusing, but otherwise there is +-- no way to directly replace a key without deletion and concatenation. +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"z":3}'::jsonb); +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"a":{"z":3}}'::jsonb); + SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb); SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb); SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); @@ -189,3 +247,6 @@ SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb); SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); + +SELECT jsonb_replace_path('{"a":{"b":3, "c":{"r":{"u":1}}}, "d":2}'::jsonb, ARRAY['a','c','r','u'],'{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":2}'::jsonb, ARRAY['a','b'], '{"a":{"b":3, "c":{"r":{"u":1}}}}'::jsonb); diff --git a/sql/jsonb_opx_sql_comparison.sql b/sql/jsonb_opx_sql_comparison.sql deleted file mode 100755 index 4bfebf6..0000000 --- a/sql/jsonb_opx_sql_comparison.sql +++ /dev/null @@ -1,59 +0,0 @@ --- 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; diff --git a/sql/jsonb_utilsx.c b/sql/jsonb_utilsx.c deleted file mode 100755 index a08829c..0000000 --- a/sql/jsonb_utilsx.c +++ /dev/null @@ -1,230 +0,0 @@ -/* - * 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 - * - * 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); - -} -- 2.39.5