From: glyn Date: Wed, 4 Mar 2015 17:22:51 +0000 (+0000) Subject: Fix some funny behaviour in hstore 1.0 style operators and cease doing anything more... X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fpg_jsonb_opx;a=commitdiff_plain;h=e22272adc71704855818856546b134733d6334a1 Fix some funny behaviour in hstore 1.0 style operators and cease doing anything more with hstore 2.0 style operators to avoid duplicating work at https://github.com/erthalion/jsonbx --- diff --git a/README.md b/README.md index 078d292..0eb0228 100755 --- a/README.md +++ b/README.md @@ -6,20 +6,44 @@ Missing operators for jsonb in PostgreSQL 9.4, this may contain some errors and Provides -------- -The following behave like hstore 1.x operators, i.e. without nested jsonb traversal +The following behave like **hstore 1.x operators**; i.e. without nested jsonb traversal * deletion using **-** operator * jsonb_delete(jsonb, text) + * jsonb_delete(jsonb, numeric) + * jsonb_delete(jsonb, boolean) * jsonb_delete(jsonb, text[]) + * jsonb_delete(jsonb, numeric[]) + * jsonb_delete(jsonb, boolean[]) * 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 +All of the above are provided with the standard extension and can be installed via CREATE EXTENSION E.g: + +```sql +CREATE EXTENSION jsonb_opx; +``` + +The following are intended to behave like **hstore 2.0 operators**; i.e. recurse into nested jsonb path. + +> As of 26/02/2015 there appears to be an effort discussed on pgsql-hackers for this type of path manipulation named jsonbx that appears to be much further ahead than my effort below. * deletion at chained path using **#-** operator * jsonb_delete_path(jsonb, text[]) * replacement at chained path using function (no operator) * jsonb_replace_path(jsonb, text[], jsonb) + +To install this extra functionality specify version 1.1 when using CREATE EXTENSION E.g: + +```sql +CREATE EXTENSION jsonb_opx VERSION '1.1'; +``` + +Or if you have version 1.0 already installed, use ALTER EXTENSION E.g: + +```sql +ALTER EXTENSION jsonb_opx UPDATE TO '1.1'; +``` diff --git a/expected/jsonb_opx.out b/expected/jsonb_opx.out index e11187b..2565acd 100755 --- a/expected/jsonb_opx.out +++ b/expected/jsonb_opx.out @@ -1,4 +1,4 @@ -CREATE EXTENSION jsonb_opx; +CREATE EXTENSION jsonb_opx VERSION '1.1'; ------------------------------------------------------------------------------- -- Tests for jsonb - text ------------------------------------------------------------------------------- @@ -38,7 +38,7 @@ SELECT '["a", {"a":1}, "b"]'::jsonb - 'b'::text; ["a", {"a": 1}] (1 row) --- text deletion from array containers will only delete string types currently +-- text deletion from array containers will only delete string elements SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; ?column? ------------- @@ -152,6 +152,58 @@ SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - {"d": 2} (1 row) +-- function is strict, so - null returns null - assume SQL nulls and jsonb nulls are not equal anyway +SELECT '["1", "2", true, null]'::jsonb - null::text; + ?column? +---------- + +(1 row) + +------------------------------------------------------------------------------- +-- Tests for jsonb - numeric +------------------------------------------------------------------------------- +-- Only matches numeric array element types +SELECT '[1, "1", "2", 2]'::jsonb - 2; + ?column? +--------------- + [1, "1", "2"] +(1 row) + +SELECT '[2]'::jsonb - 2; + ?column? +---------- + [] +(1 row) + +SELECT '2'::jsonb - 2; + ?column? +---------- + [] +(1 row) + +-- Does nothing for objects +SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 2; + ?column? +-------------------------------------------- + {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} +(1 row) + +------------------------------------------------------------------------------- +-- Tests for jsonb - boolean +------------------------------------------------------------------------------- +-- Only matches boolean array element types +SELECT '[1, "1", false, true, null]'::jsonb - false; + ?column? +---------------------- + [1, "1", true, null] +(1 row) + +SELECT '[1, "1", false, true, null]'::jsonb - true; + ?column? +----------------------- + [1, "1", false, null] +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- @@ -247,7 +299,7 @@ SELECT '{"a":{"b":{"c":[1,[2,3,[4]],{"d":1}]}, "c":[1,2,3,4]}, "d":2}'::jsonb - {"d": 2} (1 row) --- simple text[] deletion from an object container should only match keys or nulls +-- simple text[] deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; ?column? -------------------------- @@ -260,17 +312,81 @@ SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; [2, {"a": 1, "b": 2}] (1 row) --- here we treat SQL nulls and json nulls as equal - bad? +-- SQL nulls and jsonb nulls are not equal SELECT '["1",null,2]'::jsonb - ARRAY[null]; + ?column? +---------------- + ["1", null, 2] +(1 row) + +SELECT '["1",2]'::jsonb - ARRAY[null]; ?column? ---------- ["1", 2] (1 row) -SELECT '["1",2]'::jsonb - ARRAY[null]; +------------------------------------------------------------------------------- +-- Tests for jsonb - numeric[] +------------------------------------------------------------------------------- +-- Only matches numeric array element types +SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[2]; + ?column? +--------------- + [1, "1", "2"] +(1 row) + +SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[1,2]; + ?column? +------------ + ["1", "2"] +(1 row) + +SELECT '[2]'::jsonb - ARRAY[1,2]; ?column? ---------- - ["1", 2] + [] +(1 row) + +SELECT '2'::jsonb - ARRAY[1,2]; + ?column? +---------- + [] +(1 row) + +-- Does nothing for objects +SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY[1,2]; + ?column? +-------------------------------------------- + {"2": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} +(1 row) + +------------------------------------------------------------------------------- +-- Tests for jsonb - boolean[] +------------------------------------------------------------------------------- +-- Only matches boolean array element types +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[false]; + ?column? +---------------------- + [1, "1", true, null] +(1 row) + +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true]; + ?column? +----------------------- + [1, "1", false, null] +(1 row) + +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false]; + ?column? +---------------- + [1, "1", null] +(1 row) + +-- Again nulls are not equal +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false, null]; + ?column? +---------------- + [1, "1", null] (1 row) ------------------------------------------------------------------------------- @@ -285,9 +401,9 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb; -- jsonb deletion from an array should only match on key SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb; - ?column? ----------- - ["c"] + ?column? +----------------- + ["a", "b", "c"] (1 row) -- jsonb deletion from nested objects should not be part matched @@ -396,13 +512,36 @@ 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" SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb; -ERROR: invalid jsonb scalar type + ?column? +---------------------------- + ["a", 2, {"a": 1, "b": 2}] +(1 row) + SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; -ERROR: invalid jsonb scalar type + ?column? +---------------------------- + ["a", 2, {"a": 1, "b": 2}] +(1 row) + +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb; + ?column? +---------- + ["a", 2] +(1 row) + +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb; + ?column? +----------------------- + [2, {"a": 1, "b": 2}] +(1 row) + +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb; + ?column? +------------------------- + ["a", {"a": 1, "b": 2}] +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb || jsonb ------------------------------------------------------------------------------- @@ -551,6 +690,12 @@ 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]}, "d": 2} (1 row) +SELECT '["a","b","c"]'::jsonb #= '{"a":1}'::jsonb; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb #- text[] ------------------------------------------------------------------------------- @@ -892,7 +1037,7 @@ SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); jsonb_replace_path -------------------- - ["b", "a"] + [null, "a"] (1 row) SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); diff --git a/jsonb_opx--1.0--1.1.sql b/jsonb_opx--1.0--1.1.sql index a1eb5ad..805575c 100755 --- a/jsonb_opx--1.0--1.1.sql +++ b/jsonb_opx--1.0--1.1.sql @@ -19,3 +19,11 @@ 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'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_append_path(jsonb, text[], jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_append_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_append_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and append to end-point key value pair with supplied jsonb'; diff --git a/jsonb_opx--1.0.sql b/jsonb_opx--1.0.sql index 5a85278..02a0a5f 100755 --- a/jsonb_opx--1.0.sql +++ b/jsonb_opx--1.0.sql @@ -10,18 +10,43 @@ 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'; +COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key or string values 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, numeric) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, numeric) IS 'delete numeric elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, numeric); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = numeric); +COMMENT ON OPERATOR - (jsonb, numeric) IS 'delete numeric elements from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, boolean) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, boolean) IS 'delete boolean elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, boolean); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = boolean); +COMMENT ON OPERATOR - (jsonb, boolean) IS 'delete boolean elements 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'; +COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys or string values in second argument from first argument'; DROP OPERATOR IF EXISTS - (jsonb, text[]); CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); @@ -29,6 +54,30 @@ COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; -- +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, numeric[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, numeric[]) IS 'delete numeric elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, numeric[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = numeric[]); +COMMENT ON OPERATOR - (jsonb, numeric[]) IS 'delete numeric elements from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, boolean[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, boolean[]) IS 'delete boolean elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, boolean[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = boolean[]); +COMMENT ON OPERATOR - (jsonb, boolean[]) IS 'delete boolean elements from left operand'; + +-- + CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, jsonb) RETURNS jsonb AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb' diff --git a/jsonb_opx--1.1.sql b/jsonb_opx--1.1.sql index 95e0807..132e0cb 100755 --- a/jsonb_opx--1.1.sql +++ b/jsonb_opx--1.1.sql @@ -10,18 +10,43 @@ 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'; +COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key or string values 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, numeric) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, numeric) IS 'delete numeric elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, numeric); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = numeric); +COMMENT ON OPERATOR - (jsonb, numeric) IS 'delete numeric elements from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, boolean) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, boolean) IS 'delete boolean elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, boolean); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = boolean); +COMMENT ON OPERATOR - (jsonb, boolean) IS 'delete boolean elements 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'; +COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys or string values in second argument from first argument'; DROP OPERATOR IF EXISTS - (jsonb, text[]); CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); @@ -29,6 +54,30 @@ COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; -- +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, numeric[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, numeric[]) IS 'delete numeric elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, numeric[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = numeric[]); +COMMENT ON OPERATOR - (jsonb, numeric[]) IS 'delete numeric elements from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, boolean[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, boolean[]) IS 'delete boolean elements in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, boolean[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = boolean[]); +COMMENT ON OPERATOR - (jsonb, boolean[]) IS 'delete boolean elements from left operand'; + +-- + CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, jsonb) RETURNS jsonb AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb' @@ -83,3 +132,10 @@ RETURNS jsonb 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'; +-- + +CREATE OR REPLACE FUNCTION jsonb_append_path(jsonb, text[], jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_append_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_append_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and append to end-point key value pair with supplied jsonb'; diff --git a/jsonb_opx.c b/jsonb_opx.c index 29ea74a..5627d95 100755 --- a/jsonb_opx.c +++ b/jsonb_opx.c @@ -14,10 +14,12 @@ #include "postgres.h" #include "fmgr.h" #include "utils/array.h" +#include "utils/numeric.h" #include "utils/jsonb.h" #include "catalog/pg_type.h" #include "utils/builtins.h" #include "jsonb_opx.h" +#include "utils/lsyscache.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; @@ -38,23 +40,39 @@ 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); - + Jsonb *input_jsonb = PG_GETARG_JSONB(0); + Oid input_element_type = get_fn_expr_argtype(fcinfo->flinfo, 1); + text *input_text = NULL; + Numeric input_numeric = NULL; + bool input_bool = false; + /* pointers to return jsonb value data and state to be converted to jsonb on return */ - JsonbParseState *state = NULL; - JsonbValue *return_jsonb_value = NULL; + JsonbParseState *state = NULL; + JsonbValue *return_jsonb_value = NULL; /* pointer to iterator for input_jsonb value data */ - JsonbIterator *jsonb_iterator; - JsonbValue jsonb_iterator_value; - int32 jsonb_iterator_token; + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; /* variables used for skip logic */ - int32 skip_level = 0; - int32 nest_level = 0; - int32 array_level = 0; - bool push = true; + int32 skip_level = 0; + int32 nest_level = 0; + int32 array_level = 0; + bool push = true; + + /* make function polymorphic for text/numeric/boolean */ + if (!OidIsValid(input_element_type)) + elog(ERROR, "could not determine data type of input"); + + if (input_element_type == TEXTOID) + input_text = PG_GETARG_TEXT_P(1); + else if (input_element_type == NUMERICOID) + input_numeric = PG_GETARG_NUMERIC(1); + else if (input_element_type == BOOLOID) + input_bool = PG_GETARG_BOOL(1); + else + elog(ERROR, "invalid data type input: %i", input_element_type); /* * If we've been supplied with an existing key iterate round json data and rebuild @@ -73,32 +91,48 @@ jsonb_delete_key(PG_FUNCTION_ARGS) 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 (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) + case WJB_BEGIN_ARRAY: + array_level++; + break; + case WJB_BEGIN_OBJECT: + nest_level++; + break; + case WJB_ELEM: + if (skip_level == 0 && nest_level == 0 && array_level == 1) { - 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)) + if ((jsonb_iterator_value.type == jbvNumeric) && (input_element_type == NUMERICOID)) { - + if (DatumGetBool(DirectFunctionCall2(numeric_eq, + PointerGetDatum(jsonb_iterator_value.val.numeric), + PointerGetDatum(input_numeric)))) + push = false; + } + else if ((jsonb_iterator_value.type == jbvBool) && (input_element_type == BOOLOID)) + { + if (jsonb_iterator_value.val.boolean == input_bool) + push = false; + } + /* sql null <> jsonb null */ + } + 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) && (input_element_type == TEXTOID)) + { + 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)) + { if (jsonb_iterator_token == WJB_ELEM) push = false; else skip_level = nest_level; + } } } - } + /* switch end */ } if (push && (skip_level == 0 || nest_level < skip_level)) @@ -108,19 +142,20 @@ jsonb_delete_key(PG_FUNCTION_ARGS) 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; + 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; + case WJB_VALUE: + if (skip_level == nest_level) + skip_level = 0; + /* switch end */ } } @@ -128,7 +163,8 @@ jsonb_delete_key(PG_FUNCTION_ARGS) return_jsonb_value->val.array.rawScalar = true; PG_FREE_IF_COPY(input_jsonb, 0); - PG_FREE_IF_COPY(input_text, 1); + if (input_element_type == TEXTOID) + PG_FREE_IF_COPY(input_text, 1); PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value)); } @@ -148,37 +184,44 @@ Datum jsonb_delete_keys(PG_FUNCTION_ARGS) { /* general loops */ - int i; + int i; /* pointers to incoming jsonb and text[] data */ - Jsonb *input_jsonb = PG_GETARG_JSONB(0); - ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1); + Jsonb *input_jsonb = PG_GETARG_JSONB(0); + ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1); + int16 array_typlen; + bool array_typbyval; + char array_typalign; + Oid array_element_type = ARR_ELEMTYPE(input_array); /* pointers to return jsonb value data and state to be converted to jsonb on return */ - JsonbParseState *state = NULL; - JsonbValue *return_jsonb_value = NULL; + JsonbParseState *state = NULL; + JsonbValue *return_jsonb_value = NULL; /* pointer to iterator for input_jsonb value data */ - JsonbIterator *jsonb_iterator; - JsonbValue jsonb_iterator_value; - int32 jsonb_iterator_token; + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; /* variables used for skip logic */ - int32 skip_level = 0; - int32 nest_level = 0; - int32 array_level = 0; - bool push = true; + int32 skip_level = 0; + int32 nest_level = 0; + int32 array_level = 0; + bool push = true; /* array element variables for use during deconstruction */ - Datum *datums; - bool *nulls; - int32 count; + Datum *datums; + bool *nulls; + int32 count; /* individual array values values from incoming text[] */ - text *array_element_text; + text *array_element_text; + Numeric array_element_numeric = NULL; + bool array_element_bool = false; - /* assert input_array is a text array type */ - Assert(ARR_ELEMTYPE(input_array) == TEXTOID); + /* ? */ + if (array_element_type != TEXTOID && array_element_type != NUMERICOID && array_element_type != BOOLOID) + elog(ERROR, "invalid data type input"); /* check input_array is one-dimensional */ if (ARR_NDIM(input_array) > 1) @@ -187,7 +230,8 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) errmsg("1 dimensional text array expected"))); /* deconstruct array elements */ - deconstruct_array(input_array, TEXTOID, -1, false, 'i', + get_typlenbyvalalign(array_element_type, &array_typlen, &array_typbyval, &array_typalign); + deconstruct_array(input_array, array_element_type, array_typlen, array_typbyval, array_typalign, &datums, &nulls, &count); /* if the array is empty there's no work to do so return the input value */ @@ -210,40 +254,80 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) 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 (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level == 1 && array_level == 0) || + case WJB_BEGIN_ARRAY: + array_level++; + break; + case WJB_BEGIN_OBJECT: + nest_level++; + break; + case WJB_ELEM: + if (skip_level == 0 && nest_level == 0 && array_level == 1) + { + if ((jsonb_iterator_value.type == jbvNumeric) && (array_element_type == NUMERICOID)) + { + for (i=0; i jsonb null */ + } + 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) { - for (i=0; iroot); - while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE) + while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true)) != WJB_DONE) { - skip_nested = true; + //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) + 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: + switch (jsonb_iterator_value.type) { - return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data); + case jbvBinary: + jsonb_iterator2 = JsonbIteratorInit(&input_jsonb_b->root); + while ((jsonb_iterator_token2 = JsonbIteratorNext(&jsonb_iterator2, &jsonb_iterator_value2, true)) != WJB_DONE) + { + if (jsonb_iterator_value2.type == jbvBinary) + { + if ((jsonb_iterator_value2.val.binary.len == jsonb_iterator_value.val.binary.len) && + (memcmp(jsonb_iterator_value2.val.binary.data, + jsonb_iterator_value.val.binary.data, + jsonb_iterator_value2.val.binary.len) == 0)) + { + push = false; + break; + } + } + } + break; + default: + if (findJsonbValueFromContainer(&input_jsonb_b->root, + JB_FARRAY, &jsonb_iterator_value) != NULL) + push = false; + /* inner switch end */ } - else + + if (push) { - return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value); + 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); + break; + case WJB_KEY: + jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT, &jsonb_iterator_value); - if (jsonb_lookup_value != NULL) - { + jsonb_iterator_key = jsonb_iterator_value; + jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true); + if (jsonb_iterator_token != WJB_VALUE) + elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token); - if (jsonb_lookup_value->type == jsonb_iterator_value.type) + if (jsonb_lookup_value != NULL) { - switch (jsonb_lookup_value->type) + if (jsonb_lookup_value->type == jsonb_iterator_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) + switch (jsonb_lookup_value->type) + { + /* Nulls within json are equal, but should not be equal to SQL nulls */ + case jbvNull: 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))); + 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: + case jbvObject: + /* should not be possible? */ + default: + ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type %i", jsonb_iterator_token))); + /* inner switch end */ + } } } - } - if (push) - { - return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key); + 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); + /* 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); + break; + case WJB_VALUE: + /* should not be possible */ + default: + elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token); + /* switch end */ } } @@ -448,11 +557,11 @@ 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_a = PG_GETARG_JSONB(0); + ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1); /* pointer to return jsonb data */ - Jsonb *return_jsonb = NULL; + Jsonb *return_jsonb = NULL; return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, NULL); @@ -477,22 +586,22 @@ 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); + 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; + 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; + 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; + int32 nest_level = 0; + bool first = true; /* * check if either supplied jsonb is empty and return the other if so @@ -599,22 +708,22 @@ 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); + 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; + 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; + 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; + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; /* * check if supplied replacement jsonb is empty and return unchanged if so @@ -693,12 +802,42 @@ 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); + 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); +} + + +Datum jsonb_append_path(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(jsonb_append_path); + +/* + * Test + * jsonb, text[], jsonb -> jsonb + * + */ +Datum +jsonb_append_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; + Jsonb *return_jsonb = NULL; return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, input_jsonb_b); diff --git a/jsonb_opx.control b/jsonb_opx.control index c1355bf..a4548f1 100755 --- a/jsonb_opx.control +++ b/jsonb_opx.control @@ -1,5 +1,5 @@ # pg_opx extension comment = 'hstore style functions and operators for jsonb' -default_version = '1.1' +default_version = '1.0' module_pathname = '$libdir/jsonb_opx' relocatable = true diff --git a/jsonb_utilsx.c b/jsonb_utilsx.c index f7a3de9..2134243 100755 --- a/jsonb_utilsx.c +++ b/jsonb_utilsx.c @@ -20,10 +20,10 @@ JsonbValue * pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container) { - JsonbIterator *jsonb_iterator; - JsonbValue jsonb_iterator_value; - int32 jsonb_iterator_token; - JsonbValue *return_jsonb_value = NULL; + 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) @@ -36,7 +36,7 @@ pushJsonbBinary(JsonbParseState **pstate, JsonbContainer *jsonb_container) JsonbValue * pushJsonbValueBlind(JsonbParseState **pstate, JsonbIteratorToken jsonb_iterator_token, JsonbValue *jsonb_iterator_value) { - JsonbValue *return_jsonb_value = NULL; + JsonbValue *return_jsonb_value = NULL; if ((jsonb_iterator_token == WJB_KEY) || (jsonb_iterator_token == WJB_VALUE) || @@ -54,34 +54,34 @@ 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; + 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; + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; - JsonbIterator *jsonb_replacement_iterator; - JsonbValue jsonb_replacement_iterator_value; - int32 jsonb_replacement_iterator_token; + 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; + 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; + 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); @@ -119,10 +119,10 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) { case WJB_BEGIN_ARRAY: array_level++; - break; + break; case WJB_BEGIN_OBJECT: nest_level++; - break; + break; case WJB_ELEM: case WJB_KEY: /* @@ -133,8 +133,7 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) 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)) && + if ((!nulls[index_on] && (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 */ @@ -143,8 +142,6 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) 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 @@ -169,7 +166,7 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_replacement_iterator_value); } } - /* otherwise assume this is the replacement for the whole element /key-value pair */ + /* otherwise assume this is the replacement for the whole element or key-value pair */ else { while ((jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false)) != WJB_DONE) { @@ -202,6 +199,7 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) } } } + /* switch end */ } if (push && (skip_level == 0 || nest_level < skip_level)) @@ -215,15 +213,16 @@ jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) nest_level--; if (skip_level == nest_level && array_level == 0) skip_level = 0; - break; + break; case WJB_END_ARRAY: array_level--; if (skip_level == nest_level && array_level == 0) skip_level = 0; - break; + break; case WJB_VALUE: if (skip_level == nest_level) skip_level = 0; + /* switch end */ } } diff --git a/sql/jsonb_opx.sql b/sql/jsonb_opx.sql index 7ad02b4..8c95f2f 100755 --- a/sql/jsonb_opx.sql +++ b/sql/jsonb_opx.sql @@ -1,4 +1,4 @@ -CREATE EXTENSION jsonb_opx; +CREATE EXTENSION jsonb_opx VERSION '1.1'; ------------------------------------------------------------------------------- -- Tests for jsonb - text @@ -11,7 +11,7 @@ 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 +-- text deletion from array containers will only delete string elements SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; -- simple text deletion from an object container @@ -41,6 +41,27 @@ 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; +-- function is strict, so - null returns null - assume SQL nulls and jsonb nulls are not equal anyway +SELECT '["1", "2", true, null]'::jsonb - null::text; + +------------------------------------------------------------------------------- +-- Tests for jsonb - numeric +------------------------------------------------------------------------------- +-- Only matches numeric array element types +SELECT '[1, "1", "2", 2]'::jsonb - 2; +SELECT '[2]'::jsonb - 2; +SELECT '2'::jsonb - 2; + +-- Does nothing for objects +SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 2; + +------------------------------------------------------------------------------- +-- Tests for jsonb - boolean +------------------------------------------------------------------------------- +-- Only matches boolean array element types +SELECT '[1, "1", false, true, null]'::jsonb - false; +SELECT '[1, "1", false, true, null]'::jsonb - true; + ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- @@ -64,14 +85,37 @@ SELECT '{"a":{"b":3, "c":[1,{"r":[null,{"u":1}]},2,3,4]}, "d":[2,[2],[1,2,{"a":2 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 +-- simple text[] deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; --- here we treat SQL nulls and json nulls as equal - bad? +-- SQL nulls and jsonb nulls are not equal SELECT '["1",null,2]'::jsonb - ARRAY[null]; SELECT '["1",2]'::jsonb - ARRAY[null]; +------------------------------------------------------------------------------- +-- Tests for jsonb - numeric[] +------------------------------------------------------------------------------- +-- Only matches numeric array element types +SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[2]; +SELECT '[1, "1", "2", 2]'::jsonb - ARRAY[1,2]; +SELECT '[2]'::jsonb - ARRAY[1,2]; +SELECT '2'::jsonb - ARRAY[1,2]; + +-- Does nothing for objects +SELECT '{"2":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY[1,2]; + +------------------------------------------------------------------------------- +-- Tests for jsonb - boolean[] +------------------------------------------------------------------------------- +-- Only matches boolean array element types +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[false]; +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true]; +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false]; + +-- Again nulls are not equal +SELECT '[1, "1", false, true, null]'::jsonb - ARRAY[true, false, null]; + ------------------------------------------------------------------------------- -- Tests for jsonb - jsonb ------------------------------------------------------------------------------- @@ -106,12 +150,12 @@ 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":false, "e":[1,2,3]}'::jsonb; SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,3]}'::jsonb; SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb; - --- 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; SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1, "b":2}]'::jsonb; +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '["a"]'::jsonb; +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[2]'::jsonb; + ------------------------------------------------------------------------------- -- Tests for jsonb || jsonb @@ -161,7 +205,7 @@ 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; - +SELECT '["a","b","c"]'::jsonb #= '{"a":1}'::jsonb; ------------------------------------------------------------------------------- -- Tests for jsonb #- text[]