git.8kb.co.uk Git - postgresql/pg_jsonb_opx/blob - sql/jsonb_opx.sql

   1 CREATE EXTENSION jsonb_opx;
   2
   3 -------------------------------------------------------------------------------
   4 -- Tests for jsonb - text
   5 -------------------------------------------------------------------------------
   6
   7 -- text deletion from array containers will only delete string types as only strings can be keys:
   8 SELECT '[1, "1", "2", 2]'::jsonb - '2'::text;
   9
  10 -- simple text deletion from an object container
  11 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
  12 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b '::text;
  13 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - 'b'::text;
  14 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - 'b'::text;
  15 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'b'::text;
  16 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - 'c'::text;
  17
  18 -- simple text deletion from an object container should only match keys
  19 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '3'::text;
  20
  21 -- others
  22 SELECT '["1", "2", true, false]'::jsonb - '2'::text;
  23 SELECT '["1", "2", "2", "2"]'::jsonb - '2'::text;
  24 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - 'a'::text;
  25 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'a'::text;
  26 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'd'::text;
  27 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - 'b'::text;
  28
  29 -------------------------------------------------------------------------------
  30 -- Tests for jsonb - text[]
  31 -------------------------------------------------------------------------------
  32
  33 -- text[] deletion from array containers will only delete string types as only strings can be keys:
  34 SELECT '[1, "1", "2", 2]'::jsonb - array['1','2'];
  35
  36 -- simple text[] deletion from an object container
  37 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b'];
  38 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a ','b ',' c'];
  39 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a','b','c'];
  40 SELECT '{"a": 1, "b": 2, "c": {"b": 3}}'::jsonb - ARRAY['a','b'];
  41 SELECT '{"a": 1, "b": 2, "c": {"b": [1,2,3]}}'::jsonb - ARRAY['a','b'];
  42 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','b'];
  43 SELECT '{"a": 1, "b": 2, "c":[1,2,3]}'::jsonb - ARRAY['a','c'];
  44 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','d'];
  45 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['b','a'];
  46 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb - ARRAY['a','d'];
  47
  48 -- simple text[] deletion from an object container should only match keys or nulls
  49 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['1',' 2'];
  50 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '{a}'::text[];
  51 SELECT '["1",2]'::jsonb - ARRAY[null];
  52 SELECT '["1",null,2]'::jsonb - ARRAY[null];
  53
  54 -------------------------------------------------------------------------------
  55 -- Tests for jsonb - jsonb
  56 -------------------------------------------------------------------------------
  57
  58 -- jsonb deletion from an object should match on key/value
  59 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
  60
  61 -- jsonb deletion from an array should only match on key
  62 SELECT '["a", "b", "c"]'::jsonb - '{"a": 4, "b": 2}'::jsonb;
  63
  64 -- jsonb deletion from nested objects should not be part matched
  65 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"a": 4, "b": 2}'::jsonb;
  66
  67 -- but a match of all nested values should narcg
  68 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": 4}}'::jsonb - '{"d": {"a": 4}, "b": 2}'::jsonb;
  69
  70 -- others
  71 SELECT '{"a": 4, "b": 2, "c": 3, "d": {"a": false}}'::jsonb - '{"d": {"a": false}, "b": 2}'::jsonb;
  72 SELECT '{"a": "test", "b": 2.2, "c": {"a": false}}'::jsonb - '{"a": "test2", "c": {"a": false}, "b": 2.2}'::jsonb;
  73 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;
  74 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;
  75 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;
  76 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;
  77 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;
  78 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;
  79 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;
  80 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;
  81 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;
  82 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;
  83 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;
  84
  85 -- known issues !!!!
  86 -- lookups of lhs values in rhs jsonb use findJsonbValueFromContainer which does not allow looking up non-scalar elements resulting in "invalid jsonb scalar type"
  87 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[[1]]'::jsonb;
  88 SELECT '["a",2,{"a":1, "b":2}]'::jsonb - '[{"a":1}]'::jsonb;
  89
  90 -------------------------------------------------------------------------------
  91 -- Tests for jsonb || jsonb
  92 -------------------------------------------------------------------------------
  93
  94 -- duplicates should automatically be removed by lower level logic
  95 SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"a": 4, "b": 2, "d": 4}'::jsonb;
  96
  97 -- concatentation of arrays
  98 SELECT '["a", "b"]'::jsonb || '["c"]'::jsonb;
  99
 100 -- concatentation of scalars and arrays should be wrapped into arrays
 101 SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
 102
 103 -- likewise concatentation of objects and arrays should be wrapped into arrays
 104 SELECT '["a", "b"]'::jsonb || '{"a": 4, "b": 2}'::jsonb;
 105
 106 -- and all concatentation should be in natural order supplied
 107 SELECT '{"a": 4, "b": 2}'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
 108
 109 -- others
 110 SELECT 'false'::jsonb || '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
 111 SELECT '["a","b"]'::jsonb || '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb;
 112 SELECT '{"a":{"b":3, "c":[1,2,3,4]}, "d":2}'::jsonb || '["a","b"]'::jsonb;
 113
 114 -------------------------------------------------------------------------------
 115 -- Tests for jsonb =# jsonb
 116 -------------------------------------------------------------------------------
 117
 118 -- any keys existing in left argument have values replaced with those from righ argument
 119 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;
 120
 121 -- note that as we are matching only keys and replacing values operation on an scalar/array elements effectively does nothing
 122 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '["a","b","c"]'::jsonb;
 123 SELECT '{"a":[1,2], "b":2, "c":12}'::jsonb #= '[1,2,3]'::jsonb;
 124 SELECT '[1,2,3]'::jsonb #= '[1,2,3,4]'::jsonb;
 125 SELECT '"a"'::jsonb #= '{"a":1, "b":2}'::jsonb;
 126 SELECT '{"a":1, "b":2}'::jsonb #= '"a"'::jsonb;
 127
 128 -------------------------------------------------------------------------------
 129 -- Tests for jsonb #- text[]
 130 -------------------------------------------------------------------------------
 131 SELECT '"a"'::jsonb #- ARRAY['b'];
 132 SELECT '["a"]'::jsonb #- ARRAY['b'];
 133 SELECT '{"a":1}'::jsonb #- ARRAY['b'];
 134
 135 SELECT '"a"'::jsonb #- ARRAY['a'];
 136 SELECT '["a"]'::jsonb #- ARRAY['a'];
 137 SELECT '{"a":1}'::jsonb #- ARRAY['a'];
 138
 139 SELECT '["a", "b"]'::jsonb #- ARRAY['a'];
 140 SELECT '{"a":1, "b":2}'::jsonb #- ARRAY['a'];
 141 SELECT '{"a":{"b":1}, "c":2}'::jsonb #- ARRAY['a'];
 142
 143 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['a'];
 144 SELECT '{"a":[1,2,3,4], "b":2}'::jsonb #- ARRAY['b'];
 145
 146 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['c'];
 147 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a'];
 148 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','c'];
 149 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b'];
 150 SELECT '{"a":{"b":[1,2,3,["a","b"]]}, "c":2}'::jsonb #- ARRAY['a','b','c'];
 151 SELECT '{"a":{"b":{"c":1}, "c":[1,2,3,["a","b"]]}, "d":3}'::jsonb #- ARRAY['a','b','c'];
 152
 153 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
 154 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','c'];
 155 SELECT '{"a":{"b":[1,2,3,["a","b"]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a',null];
 156
 157 SELECT '{"a":{"b":[1,2,3,["a",{"b":3}]], "c":[1,2,3,4]}, "d":2}'::jsonb #- ARRAY['a','b'];
 158
 159 SELECT '{"a":{"b":3, "d":[1,{"Z":[1,[2,3]]}]}}'::jsonb #- ARRAY['a','d'];
 160
 161 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['a'];
 162 SELECT '["a", {"b":[1,2,3,4,5]}, 1, "c"]'::jsonb #- ARRAY['c'];
 163 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['b'];
 164 SELECT '[1,[2,[3,[4,[5,6,7]]]],"a","b"]'::jsonb #- ARRAY['a'];
 165
 166 -- expected limitation: cannot call with path deeper than 1 on a non-object
 167 SELECT '["a", "b"]'::jsonb #- ARRAY['a','b'];
 168
 169 -------------------------------------------------------------------------------
 170 -- Tests for jsonb_replace_path jsonb text[]
 171 -------------------------------------------------------------------------------
 172 SELECT jsonb_replace_path('{"a":1, "b":2}', ARRAY['a'], '{"f":3}'::jsonb);
 173 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a'], '{"f":3}'::jsonb);
 174 SELECT jsonb_replace_path('{"a":{"b":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
 175 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','b'], '{"f":3}'::jsonb);
 176 SELECT jsonb_replace_path('{"a":{"b":1, "c":1}}', ARRAY['a','c'], '{"f":3}'::jsonb);
 177
 178 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
 179 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
 180 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,2]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
 181
 182 SELECT jsonb_replace_path('{"a":{"d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
 183 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','b'], '{"f":3}'::jsonb);
 184 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','c'], '{"f":3}'::jsonb);
 185 SELECT jsonb_replace_path('{"a":{"b":1, "c":2, "d":[1,{"Z":[1,[2,3]]}]}}', ARRAY['a','d'], '{"f":3}'::jsonb);
 186 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}}}');
 187
 188 SELECT jsonb_replace_path('"a"', ARRAY['a'], '{"f":10}'::jsonb);
 189 SELECT jsonb_replace_path('"a"', ARRAY['z'], '{"f":10}'::jsonb);
 190 SELECT jsonb_replace_path('[null, "a"]', ARRAY[null], '"b"'::jsonb);
 191 SELECT jsonb_replace_path('[1,2,3,"4"]', ARRAY['4'], '"5"'::jsonb);