From 4182f8ab210feedbd7d146aa2c6c6fa77ba4962f Mon Sep 17 00:00:00 2001 From: glyn Date: Sat, 21 Feb 2015 20:34:17 +0000 Subject: [PATCH] Add missing files --- README.md | 2 +- jsonb_opx--1.0--1.1.sql | 21 ++++ jsonb_opx--1.0.sql | 64 +++++++++++++ jsonb_opx--1.1.sql | 85 ++++++++++++++++ jsonb_opx.control | 5 + jsonb_opx.h | 7 ++ jsonb_utilsx.c | 208 ++++++++++++++++++++++++++++++++++++++++ 7 files changed, 391 insertions(+), 1 deletion(-) create mode 100755 jsonb_opx--1.0--1.1.sql create mode 100755 jsonb_opx--1.0.sql create mode 100755 jsonb_opx--1.1.sql create mode 100755 jsonb_opx.control create mode 100755 jsonb_opx.h create mode 100755 jsonb_utilsx.c diff --git a/README.md b/README.md index b1406e3..0575f0d 100755 --- a/README.md +++ b/README.md @@ -17,7 +17,7 @@ The following behave like hstore 1.x operators, i.e. without nested jsonb traver * replacement using **=#** operator * jsonb_replace(jsonb, jsonb) -The following are intended to eventually function like hstor 2.0 operators +The following are intended to eventually function like hstore 2.0 operators * deletion at chained path using **#-** operator jsonb_delete_path(jsonb, text[]) diff --git a/jsonb_opx--1.0--1.1.sql b/jsonb_opx--1.0--1.1.sql new file mode 100755 index 0000000..a1eb5ad --- /dev/null +++ b/jsonb_opx--1.0--1.1.sql @@ -0,0 +1,21 @@ +\echo Use "ALTER EXTENSION jsonb_opx UPDATE TO '1.1'" to load this file. \quit + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete_path(jsonb, text[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete_path(jsonb, text[]) IS 'follow path of keys in order supplied in array and delete end-point key value pair from jsonb'; + +DROP OPERATOR IF EXISTS #- (jsonb, text[]); +CREATE OPERATOR #- ( PROCEDURE = jsonb_delete_path, LEFTARG = jsonb, RIGHTARG = text[]); +COMMENT ON OPERATOR #- (jsonb, text[]) IS 'delete key path from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_replace_path(jsonb, text[], jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_replace_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_replace_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and replace end-point key value pair with supplied jsonb'; diff --git a/jsonb_opx--1.0.sql b/jsonb_opx--1.0.sql new file mode 100755 index 0000000..5a85278 --- /dev/null +++ b/jsonb_opx--1.0.sql @@ -0,0 +1,64 @@ +\echo Use "CREATE EXTENSION jsonb_opx" to load this file. \quit + +-- CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text) +-- RETURNS jsonb +-- AS 'SELECT jsonb_delete($1, ARRAY[$2]);' +-- LANGUAGE SQL IMMUTABLE STRICT; +-- COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; + +CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, text); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text); +COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand'; + +-- +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, text[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, text[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); +COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, jsonb); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_concat(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_concat_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments'; + +DROP OPERATOR IF EXISTS || (jsonb, jsonb); +CREATE OPERATOR || ( PROCEDURE = jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_replace(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_replace_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_replace(jsonb, jsonb) IS 'replace occurrences of second jsonb argument in first'; + +DROP OPERATOR IF EXISTS #= (jsonb, jsonb); +CREATE OPERATOR #= ( PROCEDURE = jsonb_replace, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR #= (jsonb, jsonb) IS 'replace values for matching keys in jsonb types'; diff --git a/jsonb_opx--1.1.sql b/jsonb_opx--1.1.sql new file mode 100755 index 0000000..95e0807 --- /dev/null +++ b/jsonb_opx--1.1.sql @@ -0,0 +1,85 @@ +\echo Use "CREATE EXTENSION jsonb_opx" to load this file. \quit + +-- CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text) +-- RETURNS jsonb +-- AS 'SELECT jsonb_delete($1, ARRAY[$2]);' +-- LANGUAGE SQL IMMUTABLE STRICT; +-- COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; + +CREATE OR REPLACE FUNCTION jsonb_delete (jsonb, text) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_key' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, text); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text); +COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand'; + +-- +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, text[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_keys' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, text[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); +COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_delete(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; + +DROP OPERATOR IF EXISTS - (jsonb, jsonb); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_concat(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_concat_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments'; + +DROP OPERATOR IF EXISTS || (jsonb, jsonb); +CREATE OPERATOR || ( PROCEDURE = jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_replace(jsonb, jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_replace_jsonb' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_replace(jsonb, jsonb) IS 'replace occurrences of second jsonb argument in first'; + +DROP OPERATOR IF EXISTS #= (jsonb, jsonb); +CREATE OPERATOR #= ( PROCEDURE = jsonb_replace, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR #= (jsonb, jsonb) IS 'replace values for matching keys in jsonb types'; + +-- 1.1 Extra functions start here + +CREATE OR REPLACE FUNCTION jsonb_delete_path(jsonb, text[]) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_delete_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete_path(jsonb, text[]) IS 'follow path of keys in order supplied in array and delete end-point key value pair from jsonb'; + +DROP OPERATOR IF EXISTS #- (jsonb, text[]); +CREATE OPERATOR #- ( PROCEDURE = jsonb_delete_path, LEFTARG = jsonb, RIGHTARG = text[]); +COMMENT ON OPERATOR #- (jsonb, text[]) IS 'delete key path from left operand'; + +-- + +CREATE OR REPLACE FUNCTION jsonb_replace_path(jsonb, text[], jsonb) +RETURNS jsonb + AS 'MODULE_PATHNAME', 'jsonb_replace_path' +LANGUAGE C IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_replace_path(jsonb, text[], jsonb) IS 'follow path of keys in order supplied in array and replace end-point key value pair with supplied jsonb'; + diff --git a/jsonb_opx.control b/jsonb_opx.control new file mode 100755 index 0000000..c1355bf --- /dev/null +++ b/jsonb_opx.control @@ -0,0 +1,5 @@ +# pg_opx extension +comment = 'hstore style functions and operators for jsonb' +default_version = '1.1' +module_pathname = '$libdir/jsonb_opx' +relocatable = true diff --git a/jsonb_opx.h b/jsonb_opx.h new file mode 100755 index 0000000..9696b87 --- /dev/null +++ b/jsonb_opx.h @@ -0,0 +1,7 @@ +#ifndef __JSONB_OPX_H__ +#define __JSONB_OPX_H__ + +extern JsonbValue * pushJsonbBinary(JsonbParseState *pstate, JsonbContainer *jsonb_container); +extern Jsonb * jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b); + +#endif diff --git a/jsonb_utilsx.c b/jsonb_utilsx.c new file mode 100755 index 0000000..c000609 --- /dev/null +++ b/jsonb_utilsx.c @@ -0,0 +1,208 @@ +/* + * jsonb_utilsx.c + * Test jsonb delete and concatenate operator functions for 9.4 + * + * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * Author: Glyn Astill + * + * This is purely experimentation and will contain many errors and bad form + * DO NOT USE ON PRODUCTION SYSTEMS. + * + */ + +#include "postgres.h" +#include "utils/jsonb.h" +#include "catalog/pg_type.h" +#include "utils/builtins.h" +#include "jsonb_opx.h" + +JsonbValue * +pushJsonbBinary(JsonbParseState *pstate, JsonbContainer *jsonb_container) +{ + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; + JsonbValue *return_jsonb_value = NULL; + + jsonb_iterator = JsonbIteratorInit((void *)jsonb_container); + while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) + { + return_jsonb_value = pushJsonbValue(&pstate, jsonb_iterator_token, &jsonb_iterator_value); + } + return return_jsonb_value; +} + +Jsonb * +jsonbModifyPath(Jsonb *jsonb_a, ArrayType *array_path, Jsonb *jsonb_b) +{ + /* pointers to return jsonb value data and state to be converted to jsonb on return */ + JsonbParseState *state = NULL; + JsonbValue *return_jsonb_value = NULL; + + /* pointer to iterator for input jsonb */ + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; + int32 jsonb_last_token = 0; + + JsonbIterator *jsonb_replacement_iterator; + JsonbValue jsonb_replacement_iterator_value; + int32 jsonb_replacement_iterator_token; + + /* + * array element variables for use during deconstruction + * count is the depth we will be looking from the first matching key + */ + Datum *datums; + bool *nulls; + int32 count; + + /* the current key we are looking for, starting with the first key */ + text *key_on = NULL; + //text *key_on; + int32 index_on = 0; + int32 nest_level = 0; + int32 array_level = 0; + int32 skip_level = 0; + int32 push_nest_level = 0; + bool push = true; + + /* assert input_array is a text array type */ + Assert(ARR_ELEMTYPE(array_path) == TEXTOID); + + /* check input_array is one-dimensional */ + if (ARR_NDIM(array_path) > 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("1 dimensional text array expected"))); + + /* deconstruct array elements */ + deconstruct_array(array_path, TEXTOID, -1, false, 'i', + &datums, &nulls, &count); + + /* can't follow key based paths on non objects */ + if (!JB_ROOT_IS_OBJECT(jsonb_a) && (count > 1)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot call with path deeper than 1 on a non-object"))); + + /* if the array is empty there's no work to do so return the input value */ + if ((count == 0) || (JB_ROOT_COUNT(jsonb_a) == 0)) + return jsonb_a; + + if (!nulls[index_on]) + key_on = DatumGetTextP(datums[index_on]); + + jsonb_iterator = JsonbIteratorInit(&jsonb_a->root); + + while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) + { + push = true; + + switch (jsonb_iterator_token) + { + case WJB_BEGIN_ARRAY: + array_level++; + break; + case WJB_BEGIN_OBJECT: + nest_level++; + break; + case WJB_ELEM: + case WJB_KEY: + /* + * If we are not skipping the current nest level check that the nesting level follows the array index + * and if it does check the current key. For array elements only check the root level (array_level==1). + * If there is no match we just keep on pushing + */ + if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level-1 == index_on && array_level == 0) || + (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1))) + { + if ((jsonb_iterator_value.type == jbvNull && key_on == NULL) || + (key_on != NULL && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(key_on)) && + (memcmp(jsonb_iterator_value.val.string.val, VARDATA_ANY(key_on), jsonb_iterator_value.val.string.len) == 0))) + { + /* if we have not yet reached the last index in the array / key chain move on and check the next */ + if (index_on < count-1) + { + index_on++; + if (!nulls[index_on]) + key_on = DatumGetTextP(datums[index_on]); + else + key_on = NULL; + } + /* if we have reached the last index, the we can modify this level */ + else + { + /* if jsonb_b is not null unwrap it with iterator into replacement_jsonb_value */ + if (jsonb_b != NULL) { + jsonb_replacement_iterator = JsonbIteratorInit(&jsonb_b->root); + + while ((jsonb_replacement_iterator_token = JsonbIteratorNext(&jsonb_replacement_iterator, &jsonb_replacement_iterator_value, false)) != WJB_DONE) + { + if (((jsonb_last_token == jsonb_replacement_iterator_token) && + (jsonb_last_token != WJB_VALUE)) || + ((jsonb_last_token == WJB_VALUE) && + ((jsonb_replacement_iterator_token == WJB_BEGIN_OBJECT) || + (jsonb_replacement_iterator_token == WJB_BEGIN_ARRAY)))) + { + push_nest_level++; + } + + if ((jsonb_replacement_iterator_token == WJB_KEY) || + (jsonb_replacement_iterator_token == WJB_VALUE) || + (jsonb_replacement_iterator_token == WJB_ELEM) || + (push_nest_level != 1)) + { + return_jsonb_value = pushJsonbValue(&state, jsonb_replacement_iterator_token, &jsonb_replacement_iterator_value); + } + + if (((jsonb_last_token == WJB_BEGIN_ARRAY) || + (jsonb_last_token == WJB_VALUE)) && + (jsonb_replacement_iterator_token == WJB_END_ARRAY)) + { + push_nest_level--; + } + else if (((jsonb_last_token == WJB_BEGIN_OBJECT) || + (jsonb_last_token == WJB_VALUE)) && + (jsonb_replacement_iterator_token == WJB_END_OBJECT)) + { + push_nest_level--; + } + } + } + if (jsonb_iterator_token == WJB_ELEM) + push = false; + else + skip_level = nest_level; + } + } + } + } + + if (push && (skip_level == 0 || nest_level < skip_level)) + { + return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value); + jsonb_last_token = jsonb_iterator_token; + } + + switch (jsonb_iterator_token) + { + case WJB_END_OBJECT: + nest_level--; + if (skip_level == nest_level && array_level == 0) + skip_level = 0; + break; + case WJB_END_ARRAY: + array_level--; + if (skip_level == nest_level && array_level == 0) + skip_level = 0; + break; + case WJB_VALUE: + if (skip_level == nest_level) + skip_level = 0; + } + } + + return JsonbValueToJsonb(return_jsonb_value); +} -- 2.39.2