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 <a href="http://github.com/erthalion/jsonbx" target="_blank">jsonbx</a> 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';
+```
-CREATE EXTENSION jsonb_opx;
+CREATE EXTENSION jsonb_opx VERSION '1.1';
-------------------------------------------------------------------------------
-- Tests for jsonb - 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?
-------------
{"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[]
-------------------------------------------------------------------------------
{"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?
--------------------------
[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)
-------------------------------------------------------------------------------
-- 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
{}
(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
-------------------------------------------------------------------------------
{"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[]
-------------------------------------------------------------------------------
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);
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';
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[]);
--
+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'
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[]);
--
+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'
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';
#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;
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
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))
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 */
}
}
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));
}
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)
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 */
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<count; i++)
+ {
+ if (!nulls[i])
+ {
+ array_element_numeric = DatumGetNumeric(datums[i]);
+
+ if (DatumGetBool(DirectFunctionCall2(numeric_eq,
+ PointerGetDatum(jsonb_iterator_value.val.numeric),
+ PointerGetDatum(array_element_numeric))))
+ {
+ push = false;
+ break;
+ }
+ }
+ }
+ }
+ else if ((jsonb_iterator_value.type == jbvBool) && (array_element_type == BOOLOID))
+ {
+ for (i=0; i<count; i++)
+ {
+ if (!nulls[i])
+ {
+ array_element_bool = DatumGetBool(datums[i]);
+
+ if (jsonb_iterator_value.val.boolean == array_element_bool)
+ {
+ push = false;
+ break;
+ }
+ }
+ }
+ }
+ /* 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 || jsonb_iterator_value.type == jbvNull)
{
- for (i=0; i<count; i++)
+ if (jsonb_iterator_value.type == jbvString && array_element_type == TEXTOID)
{
- if (!nulls[i])
- array_element_text = DatumGetTextP(datums[i]);
- else
- array_element_text = NULL;
-
- if (((array_element_text != NULL) && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
- (memcmp(jsonb_iterator_value.val.string.val,
- VARDATA_ANY(array_element_text),
- jsonb_iterator_value.val.string.len) == 0)) || ((array_element_text == NULL) && (jsonb_iterator_value.type == jbvNull)))
+ for (i=0; i<count; i++)
{
- if (jsonb_iterator_token == WJB_ELEM)
- push = false;
- else
- skip_level = nest_level;
- break;
+ if (!nulls[i])
+ {
+ array_element_text = DatumGetTextP(datums[i]);
+
+ /* Nulls within json are equal, but should not be equal to SQL nulls */
+ if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
+ (memcmp(jsonb_iterator_value.val.string.val,
+ VARDATA_ANY(array_element_text),
+ jsonb_iterator_value.val.string.len) == 0))
+ {
+ if (jsonb_iterator_token == WJB_ELEM)
+ push = false;
+ else
+ skip_level = nest_level;
+ break;
+ }
+ }
}
}
}
- }
+ /* switch end */
}
if (push && (skip_level == 0 || nest_level < skip_level))
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;
+ 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;
+ /* switch end */
}
}
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);
+ 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;
+ 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;
+ JsonbIterator *jsonb_iterator;
+ JsonbIterator *jsonb_iterator2;
+ JsonbValue jsonb_iterator_value;
+ JsonbValue jsonb_iterator_value2;
+ JsonbValue jsonb_iterator_key;
+ int32 jsonb_iterator_token;
+ int32 jsonb_iterator_token2;
bool push = true;
/* pointer to lookup on input_jsonb_b */
- JsonbValue *jsonb_lookup_value = NULL;
+ JsonbValue *jsonb_lookup_value = NULL;
/*
* check if either right jsonb is empty and return left if so
jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
- 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 */
}
}
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);
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
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
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);
# 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
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)
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) ||
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);
{
case WJB_BEGIN_ARRAY:
array_level++;
- break;
+ break;
case WJB_BEGIN_OBJECT:
nest_level++;
- break;
+ break;
case WJB_ELEM:
case WJB_KEY:
/*
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 */
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
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)
{
}
}
}
+ /* switch end */
}
if (push && (skip_level == 0 || nest_level < skip_level))
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 */
}
}
-CREATE EXTENSION jsonb_opx;
+CREATE EXTENSION jsonb_opx VERSION '1.1';
-------------------------------------------------------------------------------
-- Tests for jsonb - 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
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[]
-------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
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
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[]
projects / postgresql / pg_jsonb_opx / commitdiff