1-- Strings. 2SELECT '""'::jsonb; -- OK. 3SELECT $$''$$::jsonb; -- ERROR, single quotes are not allowed 4SELECT '"abc"'::jsonb; -- OK 5SELECT '"abc'::jsonb; -- ERROR, quotes not closed 6SELECT '"abc 7def"'::jsonb; -- ERROR, unescaped newline in string constant 8SELECT '"\n\"\\"'::jsonb; -- OK, legal escapes 9SELECT '"\v"'::jsonb; -- ERROR, not a valid JSON escape 10-- see json_encoding test for input with unicode escapes 11 12-- Numbers. 13SELECT '1'::jsonb; -- OK 14SELECT '0'::jsonb; -- OK 15SELECT '01'::jsonb; -- ERROR, not valid according to JSON spec 16SELECT '0.1'::jsonb; -- OK 17SELECT '9223372036854775808'::jsonb; -- OK, even though it's too large for int8 18SELECT '1e100'::jsonb; -- OK 19SELECT '1.3e100'::jsonb; -- OK 20SELECT '1f2'::jsonb; -- ERROR 21SELECT '0.x1'::jsonb; -- ERROR 22SELECT '1.3ex100'::jsonb; -- ERROR 23 24-- Arrays. 25SELECT '[]'::jsonb; -- OK 26SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb; -- OK 27SELECT '[1,2]'::jsonb; -- OK 28SELECT '[1,2,]'::jsonb; -- ERROR, trailing comma 29SELECT '[1,2'::jsonb; -- ERROR, no closing bracket 30SELECT '[1,[2]'::jsonb; -- ERROR, no closing bracket 31 32-- Objects. 33SELECT '{}'::jsonb; -- OK 34SELECT '{"abc"}'::jsonb; -- ERROR, no value 35SELECT '{"abc":1}'::jsonb; -- OK 36SELECT '{1:"abc"}'::jsonb; -- ERROR, keys must be strings 37SELECT '{"abc",1}'::jsonb; -- ERROR, wrong separator 38SELECT '{"abc"=1}'::jsonb; -- ERROR, totally wrong separator 39SELECT '{"abc"::1}'::jsonb; -- ERROR, another wrong separator 40SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::jsonb; -- OK 41SELECT '{"abc":1:2}'::jsonb; -- ERROR, colon in wrong spot 42SELECT '{"abc":1,3}'::jsonb; -- ERROR, no value 43 44-- Recursion. 45SET max_stack_depth = '100kB'; 46SELECT repeat('[', 10000)::jsonb; 47SELECT repeat('{"a":', 10000)::jsonb; 48RESET max_stack_depth; 49 50-- Miscellaneous stuff. 51SELECT 'true'::jsonb; -- OK 52SELECT 'false'::jsonb; -- OK 53SELECT 'null'::jsonb; -- OK 54SELECT ' true '::jsonb; -- OK, even with extra whitespace 55SELECT 'true false'::jsonb; -- ERROR, too many values 56SELECT 'true, false'::jsonb; -- ERROR, too many values 57SELECT 'truf'::jsonb; -- ERROR, not a keyword 58SELECT 'trues'::jsonb; -- ERROR, not a keyword 59SELECT ''::jsonb; -- ERROR, no value 60SELECT ' '::jsonb; -- ERROR, no value 61 62-- make sure jsonb is passed through json generators without being escaped 63SELECT array_to_json(ARRAY [jsonb '{"a":1}', jsonb '{"b":[2,3]}']); 64 65-- anyarray column 66 67CREATE TEMP TABLE rows AS 68SELECT x, 'txt' || x as y 69FROM generate_series(1,3) AS x; 70 71analyze rows; 72 73select attname, to_jsonb(histogram_bounds) histogram_bounds 74from pg_stats 75where tablename = 'rows' and 76 schemaname = pg_my_temp_schema()::regnamespace::text 77order by 1; 78 79-- to_jsonb, timestamps 80 81select to_jsonb(timestamp '2014-05-28 12:22:35.614298'); 82 83BEGIN; 84SET LOCAL TIME ZONE 10.5; 85select to_jsonb(timestamptz '2014-05-28 12:22:35.614298-04'); 86SET LOCAL TIME ZONE -8; 87select to_jsonb(timestamptz '2014-05-28 12:22:35.614298-04'); 88COMMIT; 89 90select to_jsonb(date '2014-05-28'); 91 92select to_jsonb(date 'Infinity'); 93select to_jsonb(date '-Infinity'); 94select to_jsonb(timestamp 'Infinity'); 95select to_jsonb(timestamp '-Infinity'); 96select to_jsonb(timestamptz 'Infinity'); 97select to_jsonb(timestamptz '-Infinity'); 98 99--jsonb_agg 100 101SELECT jsonb_agg(q) 102 FROM ( SELECT $$a$$ || x AS b, y AS c, 103 ARRAY[ROW(x.*,ARRAY[1,2,3]), 104 ROW(y.*,ARRAY[4,5,6])] AS z 105 FROM generate_series(1,2) x, 106 generate_series(4,5) y) q; 107 108SELECT jsonb_agg(q ORDER BY x, y) 109 FROM rows q; 110 111UPDATE rows SET x = NULL WHERE x = 1; 112 113SELECT jsonb_agg(q ORDER BY x NULLS FIRST, y) 114 FROM rows q; 115 116-- jsonb extraction functions 117CREATE TEMP TABLE test_jsonb ( 118 json_type text, 119 test_json jsonb 120); 121 122INSERT INTO test_jsonb VALUES 123('scalar','"a scalar"'), 124('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), 125('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}'); 126 127SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'; 128SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'; 129SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'; 130SELECT test_json -> 'field2' FROM test_jsonb WHERE json_type = 'object'; 131 132SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'; 133SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'; 134SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'; 135 136SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'; 137SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'; 138SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array'; 139SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'; 140 141SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'; 142SELECT test_json ->> 7 FROM test_jsonb WHERE json_type = 'array'; 143 144SELECT test_json ->> 'field4' FROM test_jsonb WHERE json_type = 'object'; 145SELECT test_json ->> 'field5' FROM test_jsonb WHERE json_type = 'object'; 146SELECT test_json ->> 'field6' FROM test_jsonb WHERE json_type = 'object'; 147 148SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'; 149SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'; 150SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'; 151 152SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'scalar'; 153SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'array'; 154SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object'; 155 156-- nulls 157SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object'; 158SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'object'; 159SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array'; 160SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array'; 161 162-- corner cases 163select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text; 164select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int; 165select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; 166select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; 167select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> ''; 168select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1; 169select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3; 170select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; 171select '{"a": "c", "b": null}'::jsonb -> 'b'; 172select '"foo"'::jsonb -> 1; 173select '"foo"'::jsonb -> 'z'; 174 175select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; 176select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::int; 177select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; 178select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; 179select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> ''; 180select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1; 181select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3; 182select '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; 183select '{"a": "c", "b": null}'::jsonb ->> 'b'; 184select '"foo"'::jsonb ->> 1; 185select '"foo"'::jsonb ->> 'z'; 186 187-- equality and inequality 188SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; 189SELECT '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb; 190 191SELECT '{"x":"y"}'::jsonb <> '{"x":"y"}'::jsonb; 192SELECT '{"x":"y"}'::jsonb <> '{"x":"z"}'::jsonb; 193 194-- containment 195SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b"}'); 196SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":null}'); 197SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "g":null}'); 198SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"g":null}'); 199SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"c"}'); 200SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b"}'); 201SELECT jsonb_contains('{"a":"b", "b":1, "c":null}', '{"a":"b", "c":"q"}'); 202SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}'; 203SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":null}'; 204SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "g":null}'; 205SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"g":null}'; 206SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"c"}'; 207SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b"}'; 208SELECT '{"a":"b", "b":1, "c":null}'::jsonb @> '{"a":"b", "c":"q"}'; 209 210SELECT '[1,2]'::jsonb @> '[1,2,2]'::jsonb; 211SELECT '[1,1,2]'::jsonb @> '[1,2,2]'::jsonb; 212SELECT '[[1,2]]'::jsonb @> '[[1,2,2]]'::jsonb; 213SELECT '[1,2,2]'::jsonb <@ '[1,2]'::jsonb; 214SELECT '[1,2,2]'::jsonb <@ '[1,1,2]'::jsonb; 215SELECT '[[1,2,2]]'::jsonb <@ '[[1,2]]'::jsonb; 216 217SELECT jsonb_contained('{"a":"b"}', '{"a":"b", "b":1, "c":null}'); 218SELECT jsonb_contained('{"a":"b", "c":null}', '{"a":"b", "b":1, "c":null}'); 219SELECT jsonb_contained('{"a":"b", "g":null}', '{"a":"b", "b":1, "c":null}'); 220SELECT jsonb_contained('{"g":null}', '{"a":"b", "b":1, "c":null}'); 221SELECT jsonb_contained('{"a":"c"}', '{"a":"b", "b":1, "c":null}'); 222SELECT jsonb_contained('{"a":"b"}', '{"a":"b", "b":1, "c":null}'); 223SELECT jsonb_contained('{"a":"b", "c":"q"}', '{"a":"b", "b":1, "c":null}'); 224SELECT '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 225SELECT '{"a":"b", "c":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 226SELECT '{"a":"b", "g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 227SELECT '{"g":null}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 228SELECT '{"a":"c"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 229SELECT '{"a":"b"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 230SELECT '{"a":"b", "c":"q"}'::jsonb <@ '{"a":"b", "b":1, "c":null}'; 231-- Raw scalar may contain another raw scalar, array may contain a raw scalar 232SELECT '[5]'::jsonb @> '[5]'; 233SELECT '5'::jsonb @> '5'; 234SELECT '[5]'::jsonb @> '5'; 235-- But a raw scalar cannot contain an array 236SELECT '5'::jsonb @> '[5]'; 237-- In general, one thing should always contain itself. Test array containment: 238SELECT '["9", ["7", "3"], 1]'::jsonb @> '["9", ["7", "3"], 1]'::jsonb; 239SELECT '["9", ["7", "3"], ["1"]]'::jsonb @> '["9", ["7", "3"], ["1"]]'::jsonb; 240-- array containment string matching confusion bug 241SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::jsonb @> '{"tags":["qu"]}'; 242 243-- array length 244SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); 245SELECT jsonb_array_length('[]'); 246SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}'); 247SELECT jsonb_array_length('4'); 248 249-- each 250SELECT jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); 251SELECT jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 252SELECT * FROM jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 253SELECT * FROM jsonb_each('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 254 255SELECT jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}'); 256SELECT jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 257SELECT * FROM jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; 258SELECT * FROM jsonb_each_text('{"a":{"b":"c","c":"b","1":"first"},"b":[1,2],"c":"cc","1":"first","n":null}'::jsonb) AS q; 259 260-- exists 261SELECT jsonb_exists('{"a":null, "b":"qq"}', 'a'); 262SELECT jsonb_exists('{"a":null, "b":"qq"}', 'b'); 263SELECT jsonb_exists('{"a":null, "b":"qq"}', 'c'); 264SELECT jsonb_exists('{"a":"null", "b":"qq"}', 'a'); 265SELECT jsonb '{"a":null, "b":"qq"}' ? 'a'; 266SELECT jsonb '{"a":null, "b":"qq"}' ? 'b'; 267SELECT jsonb '{"a":null, "b":"qq"}' ? 'c'; 268SELECT jsonb '{"a":"null", "b":"qq"}' ? 'a'; 269-- array exists - array elements should behave as keys 270SELECT count(*) from testjsonb WHERE j->'array' ? 'bar'; 271-- type sensitive array exists - should return no rows (since "exists" only 272-- matches strings that are either object keys or array elements) 273SELECT count(*) from testjsonb WHERE j->'array' ? '5'::text; 274-- However, a raw scalar is *contained* within the array 275SELECT count(*) from testjsonb WHERE j->'array' @> '5'::jsonb; 276 277SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['a','b']); 278SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['b','a']); 279SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['c','a']); 280SELECT jsonb_exists_any('{"a":null, "b":"qq"}', ARRAY['c','d']); 281SELECT jsonb_exists_any('{"a":null, "b":"qq"}', '{}'::text[]); 282SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['a','b']; 283SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['b','a']; 284SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['c','a']; 285SELECT jsonb '{"a":null, "b":"qq"}' ?| ARRAY['c','d']; 286SELECT jsonb '{"a":null, "b":"qq"}' ?| '{}'::text[]; 287 288SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['a','b']); 289SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['b','a']); 290SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['c','a']); 291SELECT jsonb_exists_all('{"a":null, "b":"qq"}', ARRAY['c','d']); 292SELECT jsonb_exists_all('{"a":null, "b":"qq"}', '{}'::text[]); 293SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a','b']; 294SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['b','a']; 295SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c','a']; 296SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['c','d']; 297SELECT jsonb '{"a":null, "b":"qq"}' ?& ARRAY['a','a', 'b', 'b', 'b']; 298SELECT jsonb '{"a":null, "b":"qq"}' ?& '{}'::text[]; 299 300-- typeof 301SELECT jsonb_typeof('{}') AS object; 302SELECT jsonb_typeof('{"c":3,"p":"o"}') AS object; 303SELECT jsonb_typeof('[]') AS array; 304SELECT jsonb_typeof('["a", 1]') AS array; 305SELECT jsonb_typeof('null') AS "null"; 306SELECT jsonb_typeof('1') AS number; 307SELECT jsonb_typeof('-1') AS number; 308SELECT jsonb_typeof('1.0') AS number; 309SELECT jsonb_typeof('1e2') AS number; 310SELECT jsonb_typeof('-1.0') AS number; 311SELECT jsonb_typeof('true') AS boolean; 312SELECT jsonb_typeof('false') AS boolean; 313SELECT jsonb_typeof('"hello"') AS string; 314SELECT jsonb_typeof('"true"') AS string; 315SELECT jsonb_typeof('"1.0"') AS string; 316 317-- jsonb_build_array, jsonb_build_object, jsonb_object_agg 318 319SELECT jsonb_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 320SELECT jsonb_build_array('a', NULL); -- ok 321SELECT jsonb_build_array(VARIADIC NULL::text[]); -- ok 322SELECT jsonb_build_array(VARIADIC '{}'::text[]); -- ok 323SELECT jsonb_build_array(VARIADIC '{a,b,c}'::text[]); -- ok 324SELECT jsonb_build_array(VARIADIC ARRAY['a', NULL]::text[]); -- ok 325SELECT jsonb_build_array(VARIADIC '{1,2,3,4}'::text[]); -- ok 326SELECT jsonb_build_array(VARIADIC '{1,2,3,4}'::int[]); -- ok 327SELECT jsonb_build_array(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 328 329SELECT jsonb_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'); 330 331SELECT jsonb_build_object( 332 'a', jsonb_build_object('b',false,'c',99), 333 'd', jsonb_build_object('e',array[9,8,7]::int[], 334 'f', (select row_to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r))); 335SELECT jsonb_build_object('{a,b,c}'::text[]); -- error 336SELECT jsonb_build_object('{a,b,c}'::text[], '{d,e,f}'::text[]); -- error, key cannot be array 337SELECT jsonb_build_object('a', 'b', 'c'); -- error 338SELECT jsonb_build_object(NULL, 'a'); -- error, key cannot be NULL 339SELECT jsonb_build_object('a', NULL); -- ok 340SELECT jsonb_build_object(VARIADIC NULL::text[]); -- ok 341SELECT jsonb_build_object(VARIADIC '{}'::text[]); -- ok 342SELECT jsonb_build_object(VARIADIC '{a,b,c}'::text[]); -- error 343SELECT jsonb_build_object(VARIADIC ARRAY['a', NULL]::text[]); -- ok 344SELECT jsonb_build_object(VARIADIC ARRAY[NULL, 'a']::text[]); -- error, key cannot be NULL 345SELECT jsonb_build_object(VARIADIC '{1,2,3,4}'::text[]); -- ok 346SELECT jsonb_build_object(VARIADIC '{1,2,3,4}'::int[]); -- ok 347SELECT jsonb_build_object(VARIADIC '{{1,4},{2,5},{3,6}}'::int[][]); -- ok 348 349-- empty objects/arrays 350SELECT jsonb_build_array(); 351 352SELECT jsonb_build_object(); 353 354-- make sure keys are quoted 355SELECT jsonb_build_object(1,2); 356 357-- keys must be scalar and not null 358SELECT jsonb_build_object(null,2); 359 360SELECT jsonb_build_object(r,2) FROM (SELECT 1 AS a, 2 AS b) r; 361 362SELECT jsonb_build_object(json '{"a":1,"b":2}', 3); 363 364SELECT jsonb_build_object('{1,2,3}'::int[], 3); 365 366-- handling of NULL values 367SELECT jsonb_object_agg(1, NULL::jsonb); 368SELECT jsonb_object_agg(NULL, '{"a":1}'); 369 370CREATE TEMP TABLE foo (serial_num int, name text, type text); 371INSERT INTO foo VALUES (847001,'t15','GE1043'); 372INSERT INTO foo VALUES (847002,'t16','GE1043'); 373INSERT INTO foo VALUES (847003,'sub-alpha','GESS90'); 374 375SELECT jsonb_build_object('turbines',jsonb_object_agg(serial_num,jsonb_build_object('name',name,'type',type))) 376FROM foo; 377 378SELECT jsonb_object_agg(name, type) FROM foo; 379 380INSERT INTO foo VALUES (999999, NULL, 'bar'); 381SELECT jsonb_object_agg(name, type) FROM foo; 382 383-- jsonb_object 384 385-- empty object, one dimension 386SELECT jsonb_object('{}'); 387 388-- empty object, two dimensions 389SELECT jsonb_object('{}', '{}'); 390 391-- one dimension 392SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}'); 393 394-- same but with two dimensions 395SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); 396 397-- odd number error 398SELECT jsonb_object('{a,b,c}'); 399 400-- one column error 401SELECT jsonb_object('{{a},{b}}'); 402 403-- too many columns error 404SELECT jsonb_object('{{a,b,c},{b,c,d}}'); 405 406-- too many dimensions error 407SELECT jsonb_object('{{{a,b},{c,d}},{{b,c},{d,e}}}'); 408 409--two argument form of jsonb_object 410 411select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c"}'); 412 413-- too many dimensions 414SELECT jsonb_object('{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}', '{{a,1},{b,2},{3,NULL},{"d e f","a b c"}}'); 415 416-- mismatched dimensions 417 418select jsonb_object('{a,b,c,"d e f",g}','{1,2,3,"a b c"}'); 419 420select jsonb_object('{a,b,c,"d e f"}','{1,2,3,"a b c",g}'); 421 422-- null key error 423 424select jsonb_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}'); 425 426-- empty key is allowed 427 428select jsonb_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); 429 430 431 432-- extract_path, extract_path_as_text 433SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 434SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 435SELECT jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 436SELECT jsonb_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 437SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); 438SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); 439SELECT jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); 440SELECT jsonb_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); 441 442-- extract_path nulls 443SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') IS NULL AS expect_false; 444SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') IS NULL AS expect_true; 445SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') IS NULL AS expect_false; 446SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') IS NULL AS expect_true; 447 448-- extract_path operators 449SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f4','f6']; 450SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2']; 451SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','0']; 452SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','1']; 453 454SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f4','f6']; 455SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2']; 456SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','0']; 457SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1']; 458 459-- corner cases for same 460select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}'; 461select '[1,2,3]'::jsonb #> '{}'; 462select '"foo"'::jsonb #> '{}'; 463select '42'::jsonb #> '{}'; 464select 'null'::jsonb #> '{}'; 465select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a']; 466select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', null]; 467select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a', '']; 468select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b']; 469select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c']; 470select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c','d']; 471select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','z','c']; 472select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','1','b']; 473select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b']; 474select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b']; 475select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b']; 476select '[{"b": "c"}, {"b": null}]'::jsonb #> array['1','b']; 477select '"foo"'::jsonb #> array['z']; 478select '42'::jsonb #> array['f2']; 479select '42'::jsonb #> array['0']; 480 481select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> '{}'; 482select '[1,2,3]'::jsonb #>> '{}'; 483select '"foo"'::jsonb #>> '{}'; 484select '42'::jsonb #>> '{}'; 485select 'null'::jsonb #>> '{}'; 486select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a']; 487select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', null]; 488select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a', '']; 489select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b']; 490select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c']; 491select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c','d']; 492select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','z','c']; 493select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','1','b']; 494select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b']; 495select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b']; 496select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b']; 497select '[{"b": "c"}, {"b": null}]'::jsonb #>> array['1','b']; 498select '"foo"'::jsonb #>> array['z']; 499select '42'::jsonb #>> array['f2']; 500select '42'::jsonb #>> array['0']; 501 502-- array_elements 503SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); 504SELECT * FROM jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q; 505SELECT jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]'); 506SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]') q; 507 508-- populate_record 509CREATE TYPE jbpop AS (a text, b int, c timestamp); 510 511CREATE DOMAIN jsb_int_not_null AS int NOT NULL; 512CREATE DOMAIN jsb_int_array_1d AS int[] CHECK(array_length(VALUE, 1) = 3); 513CREATE DOMAIN jsb_int_array_2d AS int[][] CHECK(array_length(VALUE, 2) = 3); 514 515create type jb_unordered_pair as (x int, y int); 516create domain jb_ordered_pair as jb_unordered_pair check((value).x <= (value).y); 517 518CREATE TYPE jsbrec AS ( 519 i int, 520 ia _int4, 521 ia1 int[], 522 ia2 int[][], 523 ia3 int[][][], 524 ia1d jsb_int_array_1d, 525 ia2d jsb_int_array_2d, 526 t text, 527 ta text[], 528 c char(10), 529 ca char(10)[], 530 ts timestamp, 531 js json, 532 jsb jsonb, 533 jsa json[], 534 rec jbpop, 535 reca jbpop[] 536); 537 538CREATE TYPE jsbrec_i_not_null AS ( 539 i jsb_int_not_null 540); 541 542SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; 543SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; 544 545SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; 546SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; 547 548SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q; 549SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q; 550SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q; 551 552SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop, '{}') q; 553 554SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"x": 43.2}') q; 555SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": null}') q; 556SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": 12345}') q; 557 558SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": null}') q; 559SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": 123}') q; 560SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [1, "2", null, 4]}') q; 561SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1, 2], [3, 4]]}') q; 562SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], 2]}') q; 563SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], [2, 3]]}') q; 564SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": "{1,2,3}"}') q; 565 566SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": null}') q; 567SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": 123}') q; 568SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [1, "2", null, 4]}') q; 569SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [[1, 2, 3]]}') q; 570 571SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": null}') q; 572SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": 123}') q; 573SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null, 4]}') q; 574SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null]}') q; 575 576SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [1, "2", null, 4]}') q; 577SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [null, 4]]}') q; 578SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[], []]}') q; 579SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [3]]}') q; 580SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], 3, 4]}') q; 581 582SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2"], [null, 4]]}') q; 583SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q; 584 585SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [1, "2", null, 4]}') q; 586SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [[1, 2], [null, 4]]}') q; 587SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q; 588SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q; 589SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q; 590SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q; 591 592SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": null}') q; 593SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": 123}') q; 594SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [1, "2", null, 4]}') q; 595SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q; 596 597SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": null}') q; 598SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaa"}') q; 599SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaa"}') q; 600SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaaaaa"}') q; 601 602SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": null}') q; 603SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": 123}') q; 604SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [1, "2", null, 4]}') q; 605SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q; 606SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q; 607 608SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": null}') q; 609SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": true}') q; 610SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": 123.45}') q; 611SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "123.45"}') q; 612SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "abc"}') q; 613SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": [123, "123", null, {"key": "value"}]}') q; 614SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q; 615 616SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": null}') q; 617SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": true}') q; 618SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": 123.45}') q; 619SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "123.45"}') q; 620SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "abc"}') q; 621SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q; 622SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q; 623 624SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": null}') q; 625SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": 123}') q; 626SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": [1, "2", null, 4]}') q; 627SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q; 628 629SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": 123}') q; 630SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": [1, 2]}') q; 631SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q; 632SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": "(abc,42,01.02.2003)"}') q; 633 634SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": 123}') q; 635SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [1, 2]}') q; 636SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q; 637SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": ["(abc,42,01.02.2003)"]}') q; 638SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q; 639 640SELECT rec FROM jsonb_populate_record( 641 row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 642 row('x',3,'2012-12-31 15:30:56')::jbpop,NULL)::jsbrec, 643 '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}' 644) q; 645 646-- anonymous record type 647SELECT jsonb_populate_record(null::record, '{"x": 0, "y": 1}'); 648SELECT jsonb_populate_record(row(1,2), '{"f1": 0, "f2": 1}'); 649SELECT * FROM 650 jsonb_populate_record(null::record, '{"x": 776}') AS (x int, y int); 651 652-- composite domain 653SELECT jsonb_populate_record(null::jb_ordered_pair, '{"x": 0, "y": 1}'); 654SELECT jsonb_populate_record(row(1,2)::jb_ordered_pair, '{"x": 0}'); 655SELECT jsonb_populate_record(row(1,2)::jb_ordered_pair, '{"x": 1, "y": 0}'); 656 657-- populate_recordset 658SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 659SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 660SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 661SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 662SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 663SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 664 665SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 666SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; 667SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; 668 669-- anonymous record type 670SELECT jsonb_populate_recordset(null::record, '[{"x": 0, "y": 1}]'); 671SELECT jsonb_populate_recordset(row(1,2), '[{"f1": 0, "f2": 1}]'); 672SELECT i, jsonb_populate_recordset(row(i,50), '[{"f1":"42"},{"f2":"43"}]') 673FROM (VALUES (1),(2)) v(i); 674SELECT * FROM 675 jsonb_populate_recordset(null::record, '[{"x": 776}]') AS (x int, y int); 676 677-- empty array is a corner case 678SELECT jsonb_populate_recordset(null::record, '[]'); 679SELECT jsonb_populate_recordset(row(1,2), '[]'); 680SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[]') q; 681SELECT * FROM 682 jsonb_populate_recordset(null::record, '[]') AS (x int, y int); 683 684-- composite domain 685SELECT jsonb_populate_recordset(null::jb_ordered_pair, '[{"x": 0, "y": 1}]'); 686SELECT jsonb_populate_recordset(row(1,2)::jb_ordered_pair, '[{"x": 0}, {"y": 3}]'); 687SELECT jsonb_populate_recordset(row(1,2)::jb_ordered_pair, '[{"x": 1, "y": 0}]'); 688 689-- negative cases where the wrong record type is supplied 690select * from jsonb_populate_recordset(row(0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 691select * from jsonb_populate_recordset(row(0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 692select * from jsonb_populate_recordset(row(0::int,0::int,0::int),'[{"a":"1","b":"2"},{"a":"3"}]') q (a text, b text); 693select * from jsonb_populate_recordset(row(1000000000::int,50::int),'[{"b":"2"},{"a":"3"}]') q (a text, b text); 694 695-- jsonb_to_record and jsonb_to_recordset 696 697select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') 698 as x(a int, b text, d text); 699 700select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') 701 as x(a int, b text, c boolean); 702 703select *, c is null as c_is_null 704from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::jsonb) 705 as t(a int, b jsonb, c text, x int, ca char(5)[], ia int[][], r jbpop); 706 707select *, c is null as c_is_null 708from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb) 709 as t(a int, b jsonb, c text, x int); 710 711select * from jsonb_to_record('{"ia": null}') as x(ia _int4); 712select * from jsonb_to_record('{"ia": 123}') as x(ia _int4); 713select * from jsonb_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4); 714select * from jsonb_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4); 715select * from jsonb_to_record('{"ia": [[1], 2]}') as x(ia _int4); 716select * from jsonb_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4); 717 718select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]); 719select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]); 720select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]); 721 722select * from jsonb_to_record('{"out": {"key": 1}}') as x(out json); 723select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out json); 724select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out json); 725select * from jsonb_to_record('{"out": {"key": 1}}') as x(out jsonb); 726select * from jsonb_to_record('{"out": [{"key": 1}]}') as x(out jsonb); 727select * from jsonb_to_record('{"out": "{\"key\": 1}"}') as x(out jsonb); 728 729-- test type info caching in jsonb_populate_record() 730CREATE TEMP TABLE jsbpoptest (js jsonb); 731 732INSERT INTO jsbpoptest 733SELECT '{ 734 "jsa": [1, "2", null, 4], 735 "rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}, 736 "reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}] 737}'::jsonb 738FROM generate_series(1, 3); 739 740SELECT (jsonb_populate_record(NULL::jsbrec, js)).* FROM jsbpoptest; 741 742DROP TYPE jsbrec; 743DROP TYPE jsbrec_i_not_null; 744DROP DOMAIN jsb_int_not_null; 745DROP DOMAIN jsb_int_array_1d; 746DROP DOMAIN jsb_int_array_2d; 747DROP DOMAIN jb_ordered_pair; 748DROP TYPE jb_unordered_pair; 749 750-- indexing 751SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; 752SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}'; 753SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}'; 754SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}'; 755SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}'; 756SELECT count(*) FROM testjsonb WHERE j ? 'public'; 757SELECT count(*) FROM testjsonb WHERE j ? 'bar'; 758SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled']; 759SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; 760SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null'; 761SELECT count(*) FROM testjsonb WHERE j @@ '"CC" == $.wait'; 762SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == "CC" && true == $.public'; 763SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25'; 764SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25.0'; 765SELECT count(*) FROM testjsonb WHERE j @@ 'exists($)'; 766SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public)'; 767SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.bar)'; 768SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public) || exists($.disabled)'; 769SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public) && exists($.disabled)'; 770SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; 771SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; 772SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; 773SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; 774SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; 775SELECT count(*) FROM testjsonb WHERE j @? '$'; 776SELECT count(*) FROM testjsonb WHERE j @? '$.public'; 777SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; 778 779CREATE INDEX jidx ON testjsonb USING gin (j); 780SET enable_seqscan = off; 781 782SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; 783SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}'; 784SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}'; 785SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}'; 786SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}'; 787SELECT count(*) FROM testjsonb WHERE j @> '{"array":["foo"]}'; 788SELECT count(*) FROM testjsonb WHERE j @> '{"array":["bar"]}'; 789-- exercise GIN_SEARCH_MODE_ALL 790SELECT count(*) FROM testjsonb WHERE j @> '{}'; 791SELECT count(*) FROM testjsonb WHERE j ? 'public'; 792SELECT count(*) FROM testjsonb WHERE j ? 'bar'; 793SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled']; 794SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled']; 795 796EXPLAIN (COSTS OFF) 797SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null'; 798SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null'; 799SELECT count(*) FROM testjsonb WHERE j @@ 'exists($ ? (@.wait == null))'; 800SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.wait ? (@ == null))'; 801SELECT count(*) FROM testjsonb WHERE j @@ '"CC" == $.wait'; 802SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == "CC" && true == $.public'; 803SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25'; 804SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25.0'; 805SELECT count(*) FROM testjsonb WHERE j @@ '$.array[*] == "foo"'; 806SELECT count(*) FROM testjsonb WHERE j @@ '$.array[*] == "bar"'; 807SELECT count(*) FROM testjsonb WHERE j @@ 'exists($ ? (@.array[*] == "bar"))'; 808SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.array ? (@[*] == "bar"))'; 809SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.array[*] ? (@ == "bar"))'; 810SELECT count(*) FROM testjsonb WHERE j @@ 'exists($)'; 811SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public)'; 812SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.bar)'; 813SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public) || exists($.disabled)'; 814SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.public) && exists($.disabled)'; 815EXPLAIN (COSTS OFF) 816SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; 817SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; 818SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; 819SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; 820SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; 821SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; 822SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; 823SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; 824SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; 825SELECT count(*) FROM testjsonb WHERE j @? '$'; 826SELECT count(*) FROM testjsonb WHERE j @? '$.public'; 827SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; 828 829-- array exists - array elements should behave as keys (for GIN index scans too) 830CREATE INDEX jidx_array ON testjsonb USING gin((j->'array')); 831SELECT count(*) from testjsonb WHERE j->'array' ? 'bar'; 832-- type sensitive array exists - should return no rows (since "exists" only 833-- matches strings that are either object keys or array elements) 834SELECT count(*) from testjsonb WHERE j->'array' ? '5'::text; 835-- However, a raw scalar is *contained* within the array 836SELECT count(*) from testjsonb WHERE j->'array' @> '5'::jsonb; 837 838RESET enable_seqscan; 839 840SELECT count(*) FROM (SELECT (jsonb_each(j)).key FROM testjsonb) AS wow; 841SELECT key, count(*) FROM (SELECT (jsonb_each(j)).key FROM testjsonb) AS wow GROUP BY key ORDER BY count DESC, key; 842 843-- sort/hash 844SELECT count(distinct j) FROM testjsonb; 845SET enable_hashagg = off; 846SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2; 847SET enable_hashagg = on; 848SET enable_sort = off; 849SELECT count(*) FROM (SELECT j FROM (SELECT * FROM testjsonb UNION ALL SELECT * FROM testjsonb) js GROUP BY j) js2; 850SELECT distinct * FROM (values (jsonb '{}' || ''::text),('{}')) v(j); 851SET enable_sort = on; 852 853RESET enable_hashagg; 854RESET enable_sort; 855 856DROP INDEX jidx; 857DROP INDEX jidx_array; 858-- btree 859CREATE INDEX jidx ON testjsonb USING btree (j); 860SET enable_seqscan = off; 861 862SELECT count(*) FROM testjsonb WHERE j > '{"p":1}'; 863SELECT count(*) FROM testjsonb WHERE j = '{"pos":98, "line":371, "node":"CBA", "indexed":true}'; 864 865--gin path opclass 866DROP INDEX jidx; 867CREATE INDEX jidx ON testjsonb USING gin (j jsonb_path_ops); 868SET enable_seqscan = off; 869 870SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}'; 871SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}'; 872SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC", "public":true}'; 873SELECT count(*) FROM testjsonb WHERE j @> '{"age":25}'; 874SELECT count(*) FROM testjsonb WHERE j @> '{"age":25.0}'; 875-- exercise GIN_SEARCH_MODE_ALL 876SELECT count(*) FROM testjsonb WHERE j @> '{}'; 877 878SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null'; 879SELECT count(*) FROM testjsonb WHERE j @@ 'exists($ ? (@.wait == null))'; 880SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.wait ? (@ == null))'; 881SELECT count(*) FROM testjsonb WHERE j @@ '"CC" == $.wait'; 882SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == "CC" && true == $.public'; 883SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25'; 884SELECT count(*) FROM testjsonb WHERE j @@ '$.age == 25.0'; 885SELECT count(*) FROM testjsonb WHERE j @@ '$.array[*] == "foo"'; 886SELECT count(*) FROM testjsonb WHERE j @@ '$.array[*] == "bar"'; 887SELECT count(*) FROM testjsonb WHERE j @@ 'exists($ ? (@.array[*] == "bar"))'; 888SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.array ? (@[*] == "bar"))'; 889SELECT count(*) FROM testjsonb WHERE j @@ 'exists($.array[*] ? (@ == "bar"))'; 890SELECT count(*) FROM testjsonb WHERE j @@ 'exists($)'; 891 892EXPLAIN (COSTS OFF) 893SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; 894SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)'; 895SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? ("CC" == @)'; 896SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.wait == "CC" && true == @.public)'; 897SELECT count(*) FROM testjsonb WHERE j @? '$.age ? (@ == 25)'; 898SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.age == 25.0)'; 899SELECT count(*) FROM testjsonb WHERE j @? '$ ? (@.array[*] == "bar")'; 900SELECT count(*) FROM testjsonb WHERE j @? '$.array ? (@[*] == "bar")'; 901SELECT count(*) FROM testjsonb WHERE j @? '$.array[*] ? (@ == "bar")'; 902SELECT count(*) FROM testjsonb WHERE j @? '$'; 903SELECT count(*) FROM testjsonb WHERE j @? '$.public'; 904SELECT count(*) FROM testjsonb WHERE j @? '$.bar'; 905 906RESET enable_seqscan; 907DROP INDEX jidx; 908 909-- nested tests 910SELECT '{"ff":{"a":12,"b":16}}'::jsonb; 911SELECT '{"ff":{"a":12,"b":16},"qq":123}'::jsonb; 912SELECT '{"aa":["a","aaa"],"qq":{"a":12,"b":16,"c":["c1","c2"],"d":{"d1":"d1","d2":"d2","d1":"d3"}}}'::jsonb; 913SELECT '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2"],"d":{"d1":"d1","d2":"d2"}}}'::jsonb; 914SELECT '{"aa":["a","aaa"],"qq":{"a":"12","b":"16","c":["c1","c2",["c3"],{"c4":4}],"d":{"d1":"d1","d2":"d2"}}}'::jsonb; 915SELECT '{"ff":["a","aaa"]}'::jsonb; 916 917SELECT 918 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'ff', 919 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'qq', 920 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'Y') IS NULL AS f, 921 ('{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb ->> 'Y') IS NULL AS t, 922 '{"ff":{"a":12,"b":16},"qq":123,"x":[1,2],"Y":null}'::jsonb -> 'x'; 923 924-- nested containment 925SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1,2]}'; 926SELECT '{"a":[2,1],"c":"b"}'::jsonb @> '{"a":[1,2]}'; 927SELECT '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":[1,2]}'; 928SELECT '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":[1,2]}'; 929SELECT '{"a":{"1":2},"c":"b"}'::jsonb @> '{"a":{"1":2}}'; 930SELECT '{"a":{"2":1},"c":"b"}'::jsonb @> '{"a":{"1":2}}'; 931SELECT '["a","b"]'::jsonb @> '["a","b","c","b"]'; 932SELECT '["a","b","c","b"]'::jsonb @> '["a","b"]'; 933SELECT '["a","b","c",[1,2]]'::jsonb @> '["a",[1,2]]'; 934SELECT '["a","b","c",[1,2]]'::jsonb @> '["b",[1,2]]'; 935 936SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[1]}'; 937SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[2]}'; 938SELECT '{"a":[1,2],"c":"b"}'::jsonb @> '{"a":[3]}'; 939 940SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"c":3}]}'; 941SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4}]}'; 942SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},3]}'; 943SELECT '{"a":[1,2,{"c":3,"x":4}],"c":"b"}'::jsonb @> '{"a":[{"x":4},1]}'; 944 945-- check some corner cases for indexed nested containment (bug #13756) 946create temp table nestjsonb (j jsonb); 947insert into nestjsonb (j) values ('{"a":[["b",{"x":1}],["b",{"x":2}]],"c":3}'); 948insert into nestjsonb (j) values ('[[14,2,3]]'); 949insert into nestjsonb (j) values ('[1,[14,2,3]]'); 950create index on nestjsonb using gin(j jsonb_path_ops); 951 952set enable_seqscan = on; 953set enable_bitmapscan = off; 954select * from nestjsonb where j @> '{"a":[[{"x":2}]]}'::jsonb; 955select * from nestjsonb where j @> '{"c":3}'; 956select * from nestjsonb where j @> '[[14]]'; 957set enable_seqscan = off; 958set enable_bitmapscan = on; 959select * from nestjsonb where j @> '{"a":[[{"x":2}]]}'::jsonb; 960select * from nestjsonb where j @> '{"c":3}'; 961select * from nestjsonb where j @> '[[14]]'; 962reset enable_seqscan; 963reset enable_bitmapscan; 964 965-- nested object field / array index lookup 966SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'n'; 967SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'a'; 968SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'b'; 969SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'c'; 970SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd'; 971SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'd' -> '1'; 972SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 'e'; 973SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb -> 0; --expecting error 974 975SELECT '["a","b","c",[1,2],null]'::jsonb -> 0; 976SELECT '["a","b","c",[1,2],null]'::jsonb -> 1; 977SELECT '["a","b","c",[1,2],null]'::jsonb -> 2; 978SELECT '["a","b","c",[1,2],null]'::jsonb -> 3; 979SELECT '["a","b","c",[1,2],null]'::jsonb -> 3 -> 1; 980SELECT '["a","b","c",[1,2],null]'::jsonb -> 4; 981SELECT '["a","b","c",[1,2],null]'::jsonb -> 5; 982SELECT '["a","b","c",[1,2],null]'::jsonb -> -1; 983SELECT '["a","b","c",[1,2],null]'::jsonb -> -5; 984SELECT '["a","b","c",[1,2],null]'::jsonb -> -6; 985 986--nested path extraction 987SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{0}'; 988SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{a}'; 989SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c}'; 990SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,0}'; 991SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,1}'; 992SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,2}'; 993SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,3}'; 994SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-1}'; 995SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-3}'; 996SELECT '{"a":"b","c":[1,2,3]}'::jsonb #> '{c,-4}'; 997 998SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{0}'; 999SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{3}'; 1000SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4}'; 1001SELECT '[0,1,2,[3,4],{"5":"five"}]'::jsonb #> '{4,5}'; 1002 1003--nested exists 1004SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'n'; 1005SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'a'; 1006SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'b'; 1007SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'c'; 1008SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'd'; 1009SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e'; 1010 1011-- jsonb_strip_nulls 1012 1013select jsonb_strip_nulls(null); 1014 1015select jsonb_strip_nulls('1'); 1016 1017select jsonb_strip_nulls('"a string"'); 1018 1019select jsonb_strip_nulls('null'); 1020 1021select jsonb_strip_nulls('[1,2,null,3,4]'); 1022 1023select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'); 1024 1025select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]'); 1026 1027-- an empty object is not null and should not be stripped 1028select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }'); 1029 1030 1031select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}'); 1032select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]'); 1033select jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}'); 1034 1035select jsonb_concat('{"d": "test", "a": [1, 2]}', '{"g": "test2", "c": {"c1":1, "c2":2}}'); 1036 1037select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"cq":"l", "b":"g", "fg":false}'; 1038select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aq":"l"}'; 1039select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{"aa":"l"}'; 1040select '{"aa":1 , "b":2, "cq":3}'::jsonb || '{}'; 1041 1042select '["a", "b"]'::jsonb || '["c"]'; 1043select '["a", "b"]'::jsonb || '["c", "d"]'; 1044select '["c"]' || '["a", "b"]'::jsonb; 1045 1046select '["a", "b"]'::jsonb || '"c"'; 1047select '"c"' || '["a", "b"]'::jsonb; 1048 1049select '[]'::jsonb || '["a"]'::jsonb; 1050select '[]'::jsonb || '"a"'::jsonb; 1051select '"b"'::jsonb || '"a"'::jsonb; 1052select '{}'::jsonb || '{"a":"b"}'::jsonb; 1053select '[]'::jsonb || '{"a":"b"}'::jsonb; 1054select '{"a":"b"}'::jsonb || '[]'::jsonb; 1055 1056select '"a"'::jsonb || '{"a":1}'; 1057select '{"a":1}' || '"a"'::jsonb; 1058 1059select '[3]'::jsonb || '{}'::jsonb; 1060select '3'::jsonb || '[]'::jsonb; 1061select '3'::jsonb || '4'::jsonb; 1062select '3'::jsonb || '{}'::jsonb; 1063 1064select '["a", "b"]'::jsonb || '{"c":1}'; 1065select '{"c": 1}'::jsonb || '["a", "b"]'; 1066 1067select '{}'::jsonb || '{"cq":"l", "b":"g", "fg":false}'; 1068 1069select pg_column_size('{}'::jsonb || '{}'::jsonb) = pg_column_size('{}'::jsonb); 1070select pg_column_size('{"aa":1}'::jsonb || '{"b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 1071select pg_column_size('{"aa":1, "b":2}'::jsonb || '{}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 1072select pg_column_size('{}'::jsonb || '{"aa":1, "b":2}'::jsonb) = pg_column_size('{"aa":1, "b":2}'::jsonb); 1073 1074select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'a'); 1075select jsonb_delete('{"a":null , "b":2, "c":3}'::jsonb, 'a'); 1076select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'b'); 1077select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'c'); 1078select jsonb_delete('{"a":1 , "b":2, "c":3}'::jsonb, 'd'); 1079select '{"a":1 , "b":2, "c":3}'::jsonb - 'a'; 1080select '{"a":null , "b":2, "c":3}'::jsonb - 'a'; 1081select '{"a":1 , "b":2, "c":3}'::jsonb - 'b'; 1082select '{"a":1 , "b":2, "c":3}'::jsonb - 'c'; 1083select '{"a":1 , "b":2, "c":3}'::jsonb - 'd'; 1084select pg_column_size('{"a":1 , "b":2, "c":3}'::jsonb - 'b') = pg_column_size('{"a":1, "b":2}'::jsonb); 1085 1086select '["a","b","c"]'::jsonb - 3; 1087select '["a","b","c"]'::jsonb - 2; 1088select '["a","b","c"]'::jsonb - 1; 1089select '["a","b","c"]'::jsonb - 0; 1090select '["a","b","c"]'::jsonb - -1; 1091select '["a","b","c"]'::jsonb - -2; 1092select '["a","b","c"]'::jsonb - -3; 1093select '["a","b","c"]'::jsonb - -4; 1094 1095select '{"a":1 , "b":2, "c":3}'::jsonb - '{b}'::text[]; 1096select '{"a":1 , "b":2, "c":3}'::jsonb - '{c,b}'::text[]; 1097select '{"a":1 , "b":2, "c":3}'::jsonb - '{}'::text[]; 1098 1099select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]'); 1100select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]'); 1101select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]'); 1102select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]'); 1103 1104select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}'); 1105select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}'); 1106select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}'); 1107select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}'); 1108 1109select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"'); 1110select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}'); 1111 1112select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'); 1113select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'); 1114select jsonb_delete_path('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{d,1,0}'); 1115 1116select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{n}'; 1117select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{b,-1}'; 1118select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{b,-1e}'; -- invalid array subscript 1119select '{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb #- '{d,1,0}'; 1120 1121 1122-- empty structure and error conditions for delete and replace 1123 1124select '"a"'::jsonb - 'a'; -- error 1125select '{}'::jsonb - 'a'; 1126select '[]'::jsonb - 'a'; 1127select '"a"'::jsonb - 1; -- error 1128select '{}'::jsonb - 1; -- error 1129select '[]'::jsonb - 1; 1130select '"a"'::jsonb #- '{a}'; -- error 1131select '{}'::jsonb #- '{a}'; 1132select '[]'::jsonb #- '{a}'; 1133select jsonb_set('"a"','{a}','"b"'); --error 1134select jsonb_set('{}','{a}','"b"', false); 1135select jsonb_set('[]','{1}','"b"', false); 1136select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0}','[2,3,4]', false); 1137 1138-- jsonb_set adding instead of replacing 1139 1140-- prepend to array 1141select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,-33}','{"foo":123}'); 1142-- append to array 1143select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,33}','{"foo":123}'); 1144-- check nesting levels addition 1145select jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}','{b,2,33}','{"foo":123}'); 1146-- add new key 1147select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":123}'); 1148-- adding doesn't do anything if elements before last aren't present 1149select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,-33}','{"foo":123}'); 1150select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,y}','{"foo":123}'); 1151-- add to empty object 1152select jsonb_set('{}','{x}','{"foo":123}'); 1153--add to empty array 1154select jsonb_set('[]','{0}','{"foo":123}'); 1155select jsonb_set('[]','{99}','{"foo":123}'); 1156select jsonb_set('[]','{-99}','{"foo":123}'); 1157select jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"'); 1158select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'); 1159select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"'); 1160 1161-- jsonb_set_lax 1162 1163\pset null NULL 1164 1165-- pass though non nulls to jsonb_set 1166select jsonb_set_lax('{"a":1,"b":2}','{b}','5') ; 1167select jsonb_set_lax('{"a":1,"b":2}','{d}','6', true) ; 1168-- using the default treatment 1169select jsonb_set_lax('{"a":1,"b":2}','{b}',null); 1170select jsonb_set_lax('{"a":1,"b":2}','{d}',null,true); 1171-- errors 1172select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, null); 1173select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, true, 'no_such_treatment'); 1174-- explicit treatments 1175select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'raise_exception') as raise_exception; 1176select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'return_target') as return_target; 1177select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key; 1178select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null; 1179 1180\pset null 1181 1182-- jsonb_insert 1183select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); 1184select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true); 1185select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"'); 1186select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"', true); 1187select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '{"b": "value"}'); 1188select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '["value1", "value2"]'); 1189 1190-- edge cases 1191select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"'); 1192select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"', true); 1193select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"'); 1194select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"', true); 1195select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"'); 1196select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"', true); 1197select jsonb_insert('[]', '{1}', '"new_value"'); 1198select jsonb_insert('[]', '{1}', '"new_value"', true); 1199select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"'); 1200select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"', true); 1201select jsonb_insert('{"a": [0,1,2]}', '{a, 10}', '"new_value"'); 1202select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"'); 1203 1204-- jsonb_insert should be able to insert new value for objects, but not to replace 1205select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"'); 1206select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true); 1207 1208select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"'); 1209select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); 1210 1211-- jsonb to tsvector 1212select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); 1213 1214-- jsonb to tsvector with config 1215select to_tsvector('simple', '{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); 1216 1217-- jsonb to tsvector with stop words 1218select to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": ["the eee fff ggg"], "c": {"d": "hhh. iii"}}'::jsonb); 1219 1220-- jsonb to tsvector with numeric values 1221select to_tsvector('english', '{"a": "aaa in bbb ddd ccc", "b": 123, "c": 456}'::jsonb); 1222 1223-- jsonb_to_tsvector 1224select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"all"'); 1225select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"key"'); 1226select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"string"'); 1227select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"numeric"'); 1228select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"boolean"'); 1229select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["string", "numeric"]'); 1230 1231select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"all"'); 1232select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"key"'); 1233select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"string"'); 1234select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"numeric"'); 1235select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '"boolean"'); 1236select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["string", "numeric"]'); 1237 1238-- to_tsvector corner cases 1239select to_tsvector('""'::jsonb); 1240select to_tsvector('{}'::jsonb); 1241select to_tsvector('[]'::jsonb); 1242select to_tsvector('null'::jsonb); 1243 1244-- jsonb_to_tsvector corner cases 1245select jsonb_to_tsvector('""'::jsonb, '"all"'); 1246select jsonb_to_tsvector('{}'::jsonb, '"all"'); 1247select jsonb_to_tsvector('[]'::jsonb, '"all"'); 1248select jsonb_to_tsvector('null'::jsonb, '"all"'); 1249 1250select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '""'); 1251select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '{}'); 1252select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '[]'); 1253select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, 'null'); 1254select jsonb_to_tsvector('english', '{"a": "aaa in bbb", "b": 123, "c": 456, "d": true, "f": false, "g": null}'::jsonb, '["all", null]'); 1255 1256-- ts_headline for jsonb 1257select ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery('bbb & ddd & hhh')); 1258select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery('bbb & ddd & hhh')); 1259select ts_headline('{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 1260select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": "ccc1 ddd1"}, "d": ["ggg hhh", "iii jjj"]}'::jsonb, tsquery('bbb & ddd & hhh'), 'StartSel = <, StopSel = >'); 1261 1262-- corner cases for ts_headline with jsonb 1263select ts_headline('null'::jsonb, tsquery('aaa & bbb')); 1264select ts_headline('{}'::jsonb, tsquery('aaa & bbb')); 1265select ts_headline('[]'::jsonb, tsquery('aaa & bbb')); 1266 1267-- casts 1268select 'true'::jsonb::bool; 1269select '[]'::jsonb::bool; 1270select '1.0'::jsonb::float; 1271select '[1.0]'::jsonb::float; 1272select '12345'::jsonb::int4; 1273select '"hello"'::jsonb::int4; 1274select '12345'::jsonb::numeric; 1275select '{}'::jsonb::numeric; 1276select '12345.05'::jsonb::numeric; 1277select '12345.05'::jsonb::float4; 1278select '12345.05'::jsonb::float8; 1279select '12345.05'::jsonb::int2; 1280select '12345.05'::jsonb::int4; 1281select '12345.05'::jsonb::int8; 1282select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric; 1283select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4; 1284select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; 1285select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; 1286select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; 1287select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 1288