From 4df8c5cd073dfd908019ebc307918b8e7102e0a6 Mon Sep 17 00:00:00 2001 From: glyn Date: Sat, 21 Feb 2015 20:05:53 +0000 Subject: [PATCH 1/1] Had a play at making the following changes today, some will be a bit ugly as just testing and playing around: - amend sql and add extension control file - add in a basic replace function - replace value comparison logic and handle nulls - this is still lacking - move some duplicate logic out, but lots left - have a go at putting together jsonb_replace_path and jsonb_delete_path functions, these are very rough and can be done better with proper thought. --- Makefile | 7 +- README.md | 118 +-------- expected/jsonb_opx.out | 572 +++++++++++++++++++++++++++++++++++++---- jsonb_opx.c | 338 +++++++++++++++++------- jsonb_opx.sql.in | 50 ---- sql/jsonb_opx.sql | 127 ++++++++- 6 files changed, 907 insertions(+), 305 deletions(-) delete mode 100755 jsonb_opx.sql.in diff --git a/Makefile b/Makefile index eb2fdf8..ef5486d 100755 --- a/Makefile +++ b/Makefile @@ -1,8 +1,9 @@ -MODULES = jsonb_opx -DATA_built = jsonb_opx.sql -OBJS = jsonb_opx.o +MODULE_big = jsonb_opx +DATA = jsonb_opx--1.0.sql jsonb_opx--1.1.sql jsonb_opx--1.0--1.1.sql +OBJS = jsonb_opx.o jsonb_utilsx.o DOCS = README.md +EXTENSION = jsonb_opx REGRESS = jsonb_opx PG_CONFIG = pg_config diff --git a/README.md b/README.md index 7933507..b1406e3 100755 --- a/README.md +++ b/README.md @@ -1,123 +1,25 @@ jsonb_opx ========= -Missing operators for jsonb in PostgreSQL 9.4, this may contain some errors and bad form so please test that it suits your requirements before using in any production scenario. +Missing operators for jsonb in PostgreSQL 9.4, this may contain some errors and bad form as it's primarily just experimentation (i'm not a frequent C programmer; but everyone has to start somewhere right?). Please test that it suits your requirements before using in any production scenario. Provides -------- +The following behave like hstore 1.x operators, i.e. without nested jsonb traversal + * deletion using **-** operator * jsonb_delete(jsonb, text) * jsonb_delete(jsonb, text[]) * jsonb_delete(jsonb, jsonb) * concatenation using **||** operator * jsonb_concat(jsonb, jsonb) +* replacement using **=#** operator + * jsonb_replace(jsonb, jsonb) -More detail ------------ -* delete operator **"-"** provided by functions *jsonb_delete(jsonb, text) jsonb_delete(jsonb, text[]) and jsonb_delete(jsonb, jsonb)* - Provides: - jsonb - text - jsonb - text[] - jsonb - jsonb - - Note: When using text type operators on jsonb arrays only elements of type text will be deleted. E.g. - -```sql -TEST=# SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; - ?column? -------------- - [1, "1", 2] -(1 row) - -TEST=# SELECT '[1, "1", "2", 2]'::jsonb - '"2"'::text; - ?column? ------------------- - [1, "1", "2", 2] -(1 row) - -TEST=# SELECT '[1, "1", "2", 2]'::jsonb - array['2']::text[]; - ?column? -------------- - [1, "1", 2] -(1 row) - -TEST=# SELECT '[1, "1", "2", 2]'::jsonb - array['"2"']::text[]; - ?column? ------------------- - [1, "1", "2", 2] -(1 row) - -``` - - More. E.g. - -```sql -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text; - ?column? ------------------- - {"a": 1, "c": 3} -(1 row) - -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b']; - ?column? ----------- - {"c": 3} -(1 row) - -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb; - ?column? ------------------- - {"a": 1, "c": 3} -(1 row) - -TEST=# SELECT '{"a": 1, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb; - ?column? ------------------- - {"a": 1, "c": 3} -(1 row) - -TEST=# SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb; - ?column? -------------------------- - {"c": 3, "d": {"a": 4}} -(1 row) -``` - -* concatenation operator **"||"** provided by function *jsonb_concat(jsonb, jsonb)* - Provides: - jsonb || jsonb - - E.g. - -```sql -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb; - ?column? ----------------------------------- - {"a": 4, "b": 2, "c": 3, "d": 4} -(1 row) - -TEST=# SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb; - ?column? ------------------ - ["a", "b", "c"] -(1 row) - -TEST=# SELECT '[1,2,3]'::jsonb || '[3,4,5]'::jsonb; - ?column? --------------------- - [1, 2, 3, 3, 4, 5] -(1 row) - -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '[1,2,3]'::jsonb; - ?column? -------------------------------------- - [{"a": 1, "b": 2, "c": 3}, 1, 2, 3] -(1 row) +The following are intended to eventually function like hstor 2.0 operators -TEST=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '[1,2,3]'::jsonb || '"a"'::jsonb; - ?column? ------------------------------------------- - [{"a": 1, "b": 2, "c": 3}, 1, 2, 3, "a"] -(1 row) -``` +* deletion at chained path using **#-** operator + jsonb_delete_path(jsonb, text[]) +* replacement at chained path using function + jsonb_replace_path(jsonb, text[], jsonb) diff --git a/expected/jsonb_opx.out b/expected/jsonb_opx.out index b79bde9..7a923e7 100755 --- a/expected/jsonb_opx.out +++ b/expected/jsonb_opx.out @@ -1,44 +1,4 @@ -\i /usr/local/pgsql/share/contrib/jsonb_opx.sql --- CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text) --- RETURNS jsonb --- AS 'SELECT jsonb_delete($1, ARRAY[$2]);' --- LANGUAGE SQL IMMUTABLE STRICT; --- COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; -CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text) -RETURNS jsonb - AS '$libdir/jsonb_opx', 'jsonb_delete_key' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; --- DROP OPERATOR - (jsonb, text); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text); -COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand'; --- -CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, text[]) -RETURNS jsonb - AS '$libdir/jsonb_opx', 'jsonb_delete_keys' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument'; --- DROP OPERATOR - (jsonb, text[]); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); -COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; --- -CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, jsonb) -RETURNS jsonb - AS '$libdir/jsonb_opx', 'jsonb_delete_jsonb' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; --- DROP OPERATOR - (jsonb, jsonb); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb); -COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; --- -CREATE OR REPLACE FUNCTION public.jsonb_concat(jsonb, jsonb) -RETURNS jsonb - AS '$libdir/jsonb_opx', 'jsonb_concat_jsonb' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments'; --- DROP OPERATOR || (jsonb, jsonb); -CREATE OPERATOR || ( PROCEDURE = public.jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb); -COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types'; +CREATE EXTENSION jsonb_opx; ------------------------------------------------------------------------------- -- Tests for jsonb - text ------------------------------------------------------------------------------- @@ -56,6 +16,36 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text; {"a": 1, "c": 3} (1 row) +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text; + ?column? +------------------------- + {"a": 1, "c": {"b": 3}} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text; + ?column? +--------------------------------- + {"a": 1, "c": {"b": [1, 2, 3]}} +(1 row) + +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text; + ?column? +-------------------------- + {"a": 1, "c": [1, 2, 3]} +(1 row) + +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text; + ?column? +------------------ + {"a": 1, "b": 2} +(1 row) + -- simple text deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; ?column? @@ -76,6 +66,30 @@ SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text; ["1"] (1 row) +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text; + ?column? +----------------------- + [2, {"a": 1, "b": 2}] +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; + ?column? +---------- + {"d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text; + ?column? +------------------------------------ + {"a": {"b": 3, "c": [1, 2, 3, 4]}} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; + ?column? +-------------------------------------------- + {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2} +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb - text[] ------------------------------------------------------------------------------- @@ -87,19 +101,91 @@ SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; (1 row) -- simple text[] deletion from an object container -SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a',' b']; +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b']; + ?column? +---------- + {"c": 3} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c']; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c']; + ?column? +---------- + {} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b']; + ?column? +----------------- + {"c": {"b": 3}} +(1 row) + +SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b']; + ?column? +------------------------- + {"c": {"b": [1, 2, 3]}} +(1 row) + +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b']; ?column? ------------------ - {"b": 2, "c": 3} + {"c": [1, 2, 3]} +(1 row) + +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c']; + ?column? +---------- + {"b": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d']; + ?column? +------------------------------------ + {"a": {"b": 3, "c": [1, 2, 3, 4]}} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a']; + ?column? +---------- + {"d": 2} +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; + ?column? +---------- + {} (1 row) --- simple text[] deletion from an object container should only match keys +-- simple text[] deletion from an object container should only match keys or nulls SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; ?column? -------------------------- {"a": 1, "b": 2, "c": 3} (1 row) +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; + ?column? +----------------------- + [2, {"a": 1, "b": 2}] +(1 row) + +SELECT '["1",2]'::jsonb - ARRAY[null]; + ?column? +---------- + ["1", 2] +(1 row) + +SELECT '["1",null,2]'::jsonb - ARRAY[null]; + ?column? +---------- + ["1", 2] +(1 row) + ------------------------------------------------------------------------------- -- Tests for jsonb - jsonb ------------------------------------------------------------------------------- @@ -138,6 +224,84 @@ SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false {"a": 4, "c": 3} (1 row) +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb; + ?column? +--------------- + {"a": "test"} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +------------------------------------------------------------------------- + {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +------------------------------------------------------------ + {"b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +--------------------------------------------------------------- + {"a": "test", "c": {"a": false}, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +------------------------------------------------------ + {"a": "test", "b": 2.2, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":true, "e":[1,2,3]}'::jsonb; + ?column? +-------------------------------------------------------------- + {"a": "test", "b": 2.2, "c": {"a": false}, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,"a",2]}'::jsonb; + ?column? +------------------------------------------------------------------------- + {"a": "test", "b": 2.2, "c": {"a": false}, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,"a"]}'::jsonb; + ?column? +------------------------------------------------------- + {"a": "test", "b": 2.2, "c": {"a": false}, "d": true} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +-------------------------------------------------- + {"c": {"a": false}, "d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb; + ?column? +------------------------------- + {"d": true, "e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,3]}'::jsonb; + ?column? +-------------------- + {"e": [1, 2, "a"]} +(1 row) + +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb; + ?column? +---------- + {} +(1 row) + +-- known issues !!!! +-- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does not allow looking up non-scalar elements resulting in "invalid jsonb scalar type" +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb; +ERROR: invalid jsonb scalar type +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb; +ERROR: invalid jsonb scalar type ------------------------------------------------------------------------------- -- Tests for jsonb || jsonb ------------------------------------------------------------------------------- @@ -183,3 +347,323 @@ SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; [false, "a", "b", "c", "d"] (1 row) +SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb; + ?column? +-------------------------------------------------------- + ["a", "b", {"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}] +(1 row) + +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; + ?column? +-------------------------------------------------------- + [{"a": {"b": 3, "c": [1, 2, 3, 4]}, "d": 2}, "a", "b"] +(1 row) + +------------------------------------------------------------------------------- +-- Tests for jsonb =# jsonb +------------------------------------------------------------------------------- +-- any keys existing in left argument have values replaced with those from righ argument +SELECT '{"a": 1, "b": 2, "c":[1,2,3], "d":{"test":false}}'::jsonb #= '{"a": [1,2,3,4], "b": {"f":100, "j":{"k":200}}, "c": 4, "d":{"test":true}}'::jsonb; + ?column? +------------------------------------------------------------------------------------ + {"a": [1, 2, 3, 4], "b": {"f": 100, "j": {"k": 200}}, "c": 4, "d": {"test": true}} +(1 row) + +-- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing +SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb; + ?column? +-------------------------------- + {"a": [1, 2], "b": 2, "c": 12} +(1 row) + +SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb; + ?column? +-------------------------------- + {"a": [1, 2], "b": 2, "c": 12} +(1 row) + +SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb; + ?column? +----------- + [1, 2, 3] +(1 row) + +SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb; + ?column? +---------- + "a" +(1 row) + +SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; + ?column? +------------------ + {"a": 1, "b": 2} +(1 row) + +------------------------------------------------------------------------------- +-- Tests for jsonb #- text[] +------------------------------------------------------------------------------- +SELECT '"a"'::jsonb #- ARRAY['b']; + ?column? +---------- + "a" +(1 row) + +SELECT '["a"]'::jsonb #- ARRAY['b']; + ?column? +---------- + ["a"] +(1 row) + +SELECT '{"a":1}'::jsonb #- ARRAY['b']; + ?column? +---------- + {"a": 1} +(1 row) + +SELECT '"a"'::jsonb #- ARRAY['a']; + ?column? +---------- + +(1 row) + +SELECT '["a"]'::jsonb #- ARRAY['a']; + ?column? +---------- + [] +(1 row) + +SELECT '{"a":1}'::jsonb #- ARRAY['a']; + ?column? +---------- + {} +(1 row) + +SELECT '["a", "b"]'::jsonb #- ARRAY['a']; + ?column? +---------- + ["b"] +(1 row) + +SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a']; + ?column? +---------- + {"b": 2} +(1 row) + +SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a']; + ?column? +---------- + {"c": 2} +(1 row) + +SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a']; + ?column? +---------- + {"b": 2} +(1 row) + +SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b']; + ?column? +--------------------- + {"a": [1, 2, 3, 4]} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c']; + ?column? +------------------------------------- + {"a": {"b": [1, 2, 3, ["a", "b"]]}} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a']; + ?column? +---------- + {"c": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c']; + ?column? +--------------------------------------------- + {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b']; + ?column? +------------------- + {"a": {}, "c": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c']; + ?column? +--------------------------------------------- + {"a": {"b": [1, 2, 3, ["a", "b"]]}, "c": 2} +(1 row) + +SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c']; + ?column? +------------------------------------------------------ + {"a": {"b": {}, "c": [1, 2, 3, ["a", "b"]]}, "d": 3} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; + ?column? +------------------------------------ + {"a": {"c": [1, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; + ?column? +--------------------------------------------- + {"a": {"b": [1, 2, 3, ["a", "b"]]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null]; + ?column? +---------------------------------------------------------------- + {"a": {"b": [1, 2, 3, ["a", "b"]], "c": [1, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; + ?column? +------------------------------------ + {"a": {"c": [1, 2, 3, 4]}, "d": 2} +(1 row) + +SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d']; + ?column? +----------------- + {"a": {"b": 3}} +(1 row) + +SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a']; + ?column? +---------------------------------- + [{"b": [1, 2, 3, 4, 5]}, 1, "c"] +(1 row) + +SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c']; + ?column? +---------------------------------- + ["a", {"b": [1, 2, 3, 4, 5]}, 1] +(1 row) + +SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b']; + ?column? +------------------------------------ + [1, [2, [3, [4, [5, 6, 7]]]], "a"] +(1 row) + +SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; + ?column? +------------------------------------ + [1, [2, [3, [4, [5, 6, 7]]]], "b"] +(1 row) + +-- expected limitation: cannot call with path deeper than 1 on a non-object +SELECT '["a", "b"]'::jsonb #- ARRAY['a','b']; +ERROR: cannot call with path deeper than 1 on a non-object +------------------------------------------------------------------------------- +-- Tests for jsonb_replace_path jsonb text[] +------------------------------------------------------------------------------- +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------- + {"b": 2, "f": 3} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------- + {"f": 3} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------- + {"a": {"f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); + jsonb_replace_path +------------------------- + {"a": {"c": 1, "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb); + jsonb_replace_path +------------------------- + {"a": {"b": 1, "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------------------------- + {"a": {"c": 2, "d": [1, 2], "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------------------------- + {"a": {"b": 1, "d": [1, 2], "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb); + jsonb_replace_path +--------------------------------- + {"a": {"b": 1, "c": 2, "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); + jsonb_replace_path +-------------------- + {"a": {"f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb); + jsonb_replace_path +------------------------------------------------------- + {"a": {"c": 2, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb); + jsonb_replace_path +------------------------------------------------------- + {"a": {"b": 1, "d": [1, {"Z": [1, [2, 3]]}], "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); + jsonb_replace_path +--------------------------------- + {"a": {"b": 1, "c": 2, "f": 3}} +(1 row) + +SELECT jsonb_replace_path('{"a":1, "b":null, "c":[1,2,null], "c":{"d":11}, "e":{"20":[100,"c"]}}', ARRAY['c'], '{"f":[[1],2], "g":"test", "h":{"i":{"j":null}}}'); + jsonb_replace_path +--------------------------------------------------------------------------------------------------- + {"a": 1, "b": null, "e": {"20": [100, "c"]}, "f": [[1], 2], "g": "test", "h": {"i": {"j": null}}} +(1 row) + +SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb); + jsonb_replace_path +-------------------- + {"f": 10} +(1 row) + +SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); + jsonb_replace_path +-------------------- + "a" +(1 row) + +SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); + jsonb_replace_path +-------------------- + ["b", "a"] +(1 row) + +SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); + jsonb_replace_path +-------------------- + "5" +(1 row) + diff --git a/jsonb_opx.c b/jsonb_opx.c index 556028a..518c5cd 100755 --- a/jsonb_opx.c +++ b/jsonb_opx.c @@ -17,6 +17,7 @@ #include "utils/jsonb.h" #include "catalog/pg_type.h" #include "utils/builtins.h" +#include "jsonb_opx.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; @@ -45,8 +46,8 @@ jsonb_delete_key(PG_FUNCTION_ARGS) JsonbValue *return_jsonb_value = NULL; /* pointer to iterator for input_jsonb and lookup value data */ - JsonbValue jsonb_lookup_value; - JsonbValue *jsonb_value = NULL; + JsonbValue jsonb_lookup_key; + JsonbValue *jsonb_lookup_value = NULL; JsonbIterator *jsonb_iterator; JsonbValue jsonb_iterator_value; int32 jsonb_iterator_token; @@ -56,26 +57,31 @@ jsonb_delete_key(PG_FUNCTION_ARGS) int32 nest_level = 0; int32 array_level = 0; - /* if we are not deaing with an array first check to make sure the key exists - this is potentially just extra unwanted work */ + /* + * if we are not deaing with an array first check to make sure the key exists + * this is potentially just extra unwanted work + */ if (!JB_ROOT_IS_ARRAY(input_jsonb)) { - jsonb_lookup_value.type = jbvString; - jsonb_lookup_value.val.string.val = VARDATA_ANY(input_text); - jsonb_lookup_value.val.string.len = VARSIZE_ANY_EXHDR(input_text); + jsonb_lookup_key.type = jbvString; + jsonb_lookup_key.val.string.val = VARDATA_ANY(input_text); + jsonb_lookup_key.val.string.len = VARSIZE_ANY_EXHDR(input_text); - jsonb_value = findJsonbValueFromContainer(&input_jsonb->root, - JB_FOBJECT | JB_FARRAY, &jsonb_lookup_value); + jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb->root, + JB_FOBJECT | JB_FARRAY, &jsonb_lookup_key); - if (jsonb_value == NULL) + if (jsonb_lookup_value == NULL) PG_RETURN_JSONB(input_jsonb); } /* - * If we've been supplied with an existing key iterate round json data and rebuild with key/value excluded. + * If we've been supplied with an existing key iterate round json data and rebuild + * with key/element excluded. * - * skip_key, nest_level and array_level are crude counts to check if the the value for the key is closed - * and ensure we don't match on keys within nested objects. Because we are recursing into nested elements - * but blindly just pushing them onto the return value we can get away without deeper knowledge of the json? + * skip_key, nest_level and array_level are crude counts to check if the the value + * for the key is closed and ensure we don't match on keys within nested objects. + * Because we are recursing into nested elements but blindly just pushing them onto + * the return value we can get away without deeper knowledge of the json? */ jsonb_iterator = JsonbIteratorInit(&input_jsonb->root); @@ -100,8 +106,10 @@ jsonb_delete_key(PG_FUNCTION_ARGS) { if (jsonb_iterator_value.type == jbvString) { - if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len), - VARDATA_ANY(input_text), VARSIZE_ANY_EXHDR(input_text)) == 0) + if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) && + (memcmp(jsonb_iterator_value.val.string.val, + VARDATA_ANY(input_text), + jsonb_iterator_value.val.string.len) == 0)) break; } } @@ -114,10 +122,13 @@ jsonb_delete_key(PG_FUNCTION_ARGS) { skip_key++; } - else if (nest_level == 1) + else if (nest_level == 1 && array_level == 0) { - if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len), - VARDATA_ANY(input_text), VARSIZE_ANY_EXHDR(input_text)) == 0) { + if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) && + (memcmp(jsonb_iterator_value.val.string.val, + VARDATA_ANY(input_text), + jsonb_iterator_value.val.string.len) == 0)) + { skip_key++; break; } @@ -181,8 +192,6 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) JsonbValue *return_jsonb_value = NULL; /* pointer to iterator for input_jsonb and lookup value data */ - JsonbValue jsonb_lookup_value; - JsonbValue *jsonb_value = NULL; JsonbIterator *jsonb_iterator; JsonbValue jsonb_iterator_value; int32 jsonb_iterator_token; @@ -199,7 +208,6 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) /* individual array values values from incoming text[] */ text *array_element_text; - bool exists = false; /* assert input_array is a text array type */ Assert(ARR_ELEMTYPE(input_array) == TEXTOID); @@ -218,42 +226,15 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) if (count == 0) PG_RETURN_JSONB(input_jsonb); - /* if we are not deaing with an array first check to make sure the key exists - this is potentially just extra unwanted work */ - if (!JB_ROOT_IS_ARRAY(input_jsonb)) - { - for (i=0; iroot, - JB_FOBJECT | JB_FARRAY, &jsonb_lookup_value); - - if (jsonb_value != NULL) - { - exists = true; - break; - } - } - - if (!exists) - PG_RETURN_JSONB(input_jsonb); - } - /* - * If we've been supplied with existing keys iterate round json data matching those keys. + * If we've been supplied with existing keys iterate round json data and rebuild + * with keys/elements excluded. * - * skip_key, nest_level and array_level are crude counts to check if the the value for the key is closed - * and ensure we don't match on keys within nested objects. Because we are recursing into nested elements - * but blindly just pushing them onto the return value we can get away without deeper knowledge of the json? + * skip_key, nest_level and array_level are crude counts to check if the the value + * for the key is closed and ensure we don't match on keys within nested objects. + * Because we are recursing into nested elements but blindly just pushing them onto + * the return value we can get away without deeper knowledge of the json? */ - jsonb_iterator = JsonbIteratorInit(&input_jsonb->root); while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) { @@ -271,20 +252,22 @@ jsonb_delete_keys(PG_FUNCTION_ARGS) return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value); break; case WJB_ELEM: - /* only match array elements if they are text */ + /* only match array elements if they are text or null */ if (skip_key == 0 && nest_level == 0 && array_level > 0) { - if (jsonb_iterator_value.type == jbvString) + if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull) { for (i=0; iroot, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value); - if (jsonb_lookup_value == NULL) - return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value); + if (jsonb_lookup_value == NULL) + { + if (jsonb_iterator_value.type == jbvBinary) + { + return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data); + } + else + { + return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value); + } + } break; case WJB_KEY : jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value); @@ -430,29 +425,32 @@ jsonb_delete_jsonb(PG_FUNCTION_ARGS) if (jsonb_lookup_value != NULL) { + if (jsonb_lookup_value->type == jsonb_iterator_value.type) { switch (jsonb_lookup_value->type) { + case jbvNull: + push = false; + break; case jbvNumeric: - if (strcmp( - DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_lookup_value->val.numeric))), - DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_iterator_value.val.numeric))) - ) == 0) + if (DatumGetBool(DirectFunctionCall2(numeric_eq, + PointerGetDatum(jsonb_lookup_value->val.numeric), + PointerGetDatum(jsonb_iterator_value.val.numeric)))) push = false; break; case jbvString: - if (strcmp( - pnstrdup(jsonb_lookup_value->val.string.val,jsonb_lookup_value->val.string.len), - pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len) - ) == 0) + 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 (strcmp( - JsonbToCString(NULL, jsonb_lookup_value->val.binary.data, jsonb_lookup_value->val.binary.len), - JsonbToCString(NULL, jsonb_iterator_value.val.binary.data, jsonb_lookup_value->val.binary.len) - ) == 0) + 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: @@ -476,13 +474,7 @@ jsonb_delete_jsonb(PG_FUNCTION_ARGS) /* if our value is nested binary data, iterate separately pushing each val */ if (jsonb_iterator_value.type == jbvBinary) { - nest_jsonb_container_a = jsonb_iterator_value.val.binary.data; - - nest_jsonb_iterator = JsonbIteratorInit(nest_jsonb_container_a); - while ((jsonb_iterator_token = JsonbIteratorNext(&nest_jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) - { - return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value); - } + return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data); } else { @@ -504,6 +496,35 @@ jsonb_delete_jsonb(PG_FUNCTION_ARGS) PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value)); } +Datum jsonb_delete_path(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(jsonb_delete_path); + +/* + * Test + * jsonb, text[] -> jsonb + * + */ +Datum +jsonb_delete_path(PG_FUNCTION_ARGS) +{ + /* pointers to incoming jsonb and text[] data */ + Jsonb *input_jsonb_a = PG_GETARG_JSONB(0); + ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1); + //Jsonb *input_jsonb_b = PG_GETARG_JSONB(2); + + /* pointer to return jsonb data */ + Jsonb *return_jsonb = NULL; + + return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, NULL); + + PG_FREE_IF_COPY(input_jsonb_a, 0); + PG_FREE_IF_COPY(input_array, 1); + //PG_FREE_IF_COPY(input_jsonb_b, 2); + + PG_RETURN_JSONB(return_jsonb); +} + Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(jsonb_concat_jsonb); @@ -518,7 +539,7 @@ PG_FUNCTION_INFO_V1(jsonb_concat_jsonb); Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS) { - /* incoming jsonb and text[] data */ + /* incoming jsonb data */ Jsonb *input_jsonb_a = PG_GETARG_JSONB(0); Jsonb *input_jsonb_b = PG_GETARG_JSONB(1); @@ -534,6 +555,7 @@ jsonb_concat_jsonb(PG_FUNCTION_ARGS) int32 jsonb_root_close; int32 nest_level = 0; + bool first = true; /* * check if either supplied jsonb is empty and return the other if so @@ -571,39 +593,42 @@ jsonb_concat_jsonb(PG_FUNCTION_ARGS) while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) { - if (jsonb_iterator_token == jsonb_root_open) + if (jsonb_iterator_token == jsonb_root_open && first) { nest_level++; if (nest_level == 1) continue; } - else if (jsonb_iterator_token == jsonb_root_close) + else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0) { nest_level--; if (nest_level == 0) continue; } + first = false; return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value); } + first = true; nest_level = 0; jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root); while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) { - if (jsonb_iterator_token == jsonb_root_open) + if (jsonb_iterator_token == jsonb_root_open && first) { nest_level++; if (nest_level == 1) continue; } - else if (jsonb_iterator_token == jsonb_root_close) + else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0) { nest_level--; if (nest_level == 0) continue; } + first = false; return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value); } @@ -615,3 +640,124 @@ jsonb_concat_jsonb(PG_FUNCTION_ARGS) PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value)); } + +Datum jsonb_replace_jsonb(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(jsonb_replace_jsonb); + +/* + * Operator function to replace json in left operand where keys match + * in the right operand. + * + * jsonb, jsonb -> jsonb + * + */ +Datum +jsonb_replace_jsonb(PG_FUNCTION_ARGS) +{ + /* incoming jsonb data */ + Jsonb *input_jsonb_a = PG_GETARG_JSONB(0); + Jsonb *input_jsonb_b = PG_GETARG_JSONB(1); + + /* return jsonb value data to be converted to jsonb on return */ + JsonbParseState *state = NULL; + JsonbValue *return_jsonb_value = NULL; + + /* lookup jsonb value data */ + JsonbValue jsonb_lookup_key; + JsonbValue *jsonb_lookup_value = NULL; + uint32 jsonb_lookup_flags; + + /* iterator for input_jsonb_b */ + JsonbIterator *jsonb_iterator; + JsonbValue jsonb_iterator_value; + int32 jsonb_iterator_token; + + /* + * check if supplied replacement jsonb is empty and return unchanged if so + */ + if (JB_ROOT_COUNT(input_jsonb_b) == 0) + PG_RETURN_JSONB(input_jsonb_a); + + if (JB_ROOT_IS_OBJECT(input_jsonb_a)) + jsonb_lookup_flags = JB_FOBJECT; + else + jsonb_lookup_flags = JB_FOBJECT | JB_FARRAY; + + jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root); + while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true)) != WJB_DONE) + { + if ((jsonb_iterator_token == WJB_ELEM ) && (jsonb_iterator_value.type == jbvBinary)) + { + return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data); + } + else + { + return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value); + } + Assert(jsonb_iterator_token != WJB_VALUE); + + if ( jsonb_iterator_token == WJB_KEY ) + { + jsonb_lookup_key.type = jbvString; + jsonb_lookup_key.val.string.val = jsonb_iterator_value.val.string.val; + jsonb_lookup_key.val.string.len = jsonb_iterator_value.val.string.len; + + jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true); + Assert(sonb_iterator_token == WJB_VALUE); + + jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, + jsonb_lookup_flags, &jsonb_lookup_key); + + /* if there's nothing to replace push the original value */ + if (jsonb_lookup_value == NULL) + { + jsonb_lookup_value = &jsonb_iterator_value; + } + + /* if our value is nested binary data, iterate separately pushing each val */ + if (jsonb_lookup_value->type == jbvBinary) + { + return_jsonb_value = pushJsonbBinary(state, jsonb_lookup_value->val.binary.data); + } + else + { + return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, jsonb_lookup_value); + } + } + } + + PG_FREE_IF_COPY(input_jsonb_a, 0); + PG_FREE_IF_COPY(input_jsonb_b, 1); + + PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value)); +} + +Datum jsonb_replace_path(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(jsonb_replace_path); + +/* + * Test + * jsonb, text[], jsonb -> jsonb + * + */ +Datum +jsonb_replace_path(PG_FUNCTION_ARGS) +{ + /* pointers to incoming jsonb and text[] data */ + Jsonb *input_jsonb_a = PG_GETARG_JSONB(0); + ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1); + Jsonb *input_jsonb_b = PG_GETARG_JSONB(2); + + /* pointer to return jsonb data */ + Jsonb *return_jsonb = NULL; + + return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, input_jsonb_b); + + PG_FREE_IF_COPY(input_jsonb_a, 0); + PG_FREE_IF_COPY(input_array, 1); + PG_FREE_IF_COPY(input_jsonb_b, 2); + + PG_RETURN_JSONB(return_jsonb); +} diff --git a/jsonb_opx.sql.in b/jsonb_opx.sql.in deleted file mode 100755 index b5477b3..0000000 --- a/jsonb_opx.sql.in +++ /dev/null @@ -1,50 +0,0 @@ --- CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text) --- RETURNS jsonb --- AS 'SELECT jsonb_delete($1, ARRAY[$2]);' --- LANGUAGE SQL IMMUTABLE STRICT; --- COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; - -CREATE OR REPLACE FUNCTION public.jsonb_delete (jsonb, text) -RETURNS jsonb - AS 'MODULE_PATHNAME', 'jsonb_delete_key' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, text) IS 'delete key in second argument from first argument'; - --- DROP OPERATOR - (jsonb, text); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text); -COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand'; - --- -CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, text[]) -RETURNS jsonb - AS 'MODULE_PATHNAME', 'jsonb_delete_keys' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, text[]) IS 'delete keys in second argument from first argument'; - --- DROP OPERATOR - (jsonb, text[]); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = text[]); -COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; - --- - -CREATE OR REPLACE FUNCTION public.jsonb_delete(jsonb, jsonb) -RETURNS jsonb - AS 'MODULE_PATHNAME', 'jsonb_delete_jsonb' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_delete(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; - --- DROP OPERATOR - (jsonb, jsonb); -CREATE OPERATOR - ( PROCEDURE = public.jsonb_delete, LEFTARG = jsonb, RIGHTARG = jsonb); -COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; - --- - -CREATE OR REPLACE FUNCTION public.jsonb_concat(jsonb, jsonb) -RETURNS jsonb - AS 'MODULE_PATHNAME', 'jsonb_concat_jsonb' -LANGUAGE C IMMUTABLE STRICT; -COMMENT ON FUNCTION public.jsonb_concat(jsonb, jsonb) IS 'concatenate first and second jsonb arguments'; - --- DROP OPERATOR || (jsonb, jsonb); -CREATE OPERATOR || ( PROCEDURE = public.jsonb_concat, LEFTARG = jsonb, RIGHTARG = jsonb); -COMMENT ON OPERATOR || (jsonb, jsonb) IS 'concatenate jsonb types'; diff --git a/sql/jsonb_opx.sql b/sql/jsonb_opx.sql index c89f5c5..a270fe9 100755 --- a/sql/jsonb_opx.sql +++ b/sql/jsonb_opx.sql @@ -1,4 +1,4 @@ -\i /usr/local/pgsql/share/contrib/jsonb_opx.sql +CREATE EXTENSION jsonb_opx; ------------------------------------------------------------------------------- -- Tests for jsonb - text @@ -9,6 +9,11 @@ SELECT '[1, "1", "2", 2]'::jsonb - '2'::text; -- simple text deletion from an object container SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text; +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text; +SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text; +SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text; +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text; +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text; -- simple text deletion from an object container should only match keys SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; @@ -16,6 +21,10 @@ SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text; -- others SELECT '["1", "2", true, false]'::jsonb - '2'::text; SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text; +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text; ------------------------------------------------------------------------------- -- Tests for jsonb - text[] @@ -25,10 +34,22 @@ SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text; SELECT '[1, "1", "2", 2]'::jsonb - array['1','2']; -- simple text[] deletion from an object container -SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a',' b']; - --- simple text[] deletion from an object container should only match keys +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b']; +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c']; +SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c']; +SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b']; +SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b']; +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b']; +SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c']; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d']; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a']; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d']; + +-- simple text[] deletion from an object container should only match keys or nulls SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2']; +SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[]; +SELECT '["1",2]'::jsonb - ARRAY[null]; +SELECT '["1",null,2]'::jsonb - ARRAY[null]; ------------------------------------------------------------------------------- -- Tests for jsonb - jsonb @@ -48,6 +69,23 @@ SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": -- others SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": false}, "d":false, "e":[1,2,3]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":true, "e":[1,2,3]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,"a",2]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test2", "b": 2.3, "c": {"a": true}, "d":false, "e":[1,2,"a"]}'::jsonb; +SELECT '{"a": "test", "b": 2.2, "c": {"a": false}, "d":true, "e":[1,2,"a"]}'::jsonb - '{"a": "test", "b": 2.2, "c": {"a": true}, "d":false, "e":[1,2,3]}'::jsonb; +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; ------------------------------------------------------------------------------- -- Tests for jsonb || jsonb @@ -70,3 +108,84 @@ SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; -- others SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; +SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb; +SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb; + +------------------------------------------------------------------------------- +-- Tests for jsonb =# jsonb +------------------------------------------------------------------------------- + +-- any keys existing in left argument have values replaced with those from righ argument +SELECT '{"a": 1, "b": 2, "c":[1,2,3], "d":{"test":false}}'::jsonb #= '{"a": [1,2,3,4], "b": {"f":100, "j":{"k":200}}, "c": 4, "d":{"test":true}}'::jsonb; + +-- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing +SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb; +SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb; +SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb; +SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb; +SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb; + +------------------------------------------------------------------------------- +-- Tests for jsonb #- text[] +------------------------------------------------------------------------------- +SELECT '"a"'::jsonb #- ARRAY['b']; +SELECT '["a"]'::jsonb #- ARRAY['b']; +SELECT '{"a":1}'::jsonb #- ARRAY['b']; + +SELECT '"a"'::jsonb #- ARRAY['a']; +SELECT '["a"]'::jsonb #- ARRAY['a']; +SELECT '{"a":1}'::jsonb #- ARRAY['a']; + +SELECT '["a", "b"]'::jsonb #- ARRAY['a']; +SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a']; +SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a']; + +SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a']; +SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b']; + +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c']; +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a']; +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c']; +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b']; +SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c']; +SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c']; + +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c']; +SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null]; + +SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b']; + +SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d']; + +SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a']; +SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c']; +SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b']; +SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a']; + +-- expected limitation: cannot call with path deeper than 1 on a non-object +SELECT '["a", "b"]'::jsonb #- ARRAY['a','b']; + +------------------------------------------------------------------------------- +-- Tests for jsonb_replace_path jsonb text[] +------------------------------------------------------------------------------- +SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb); + +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb); + +SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb); +SELECT jsonb_replace_path('{"a":1, "b":null, "c":[1,2,null], "c":{"d":11}, "e":{"20":[100,"c"]}}', ARRAY['c'], '{"f":[[1],2], "g":"test", "h":{"i":{"j":null}}}'); + +SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb); +SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb); +SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb); +SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb); -- 2.39.5