diff options
Diffstat (limited to 'src/test/regress/sql/json.sql')
| -rw-r--r-- | src/test/regress/sql/json.sql | 171 |
1 files changed, 171 insertions, 0 deletions
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index 5583d653075..04b22fe297e 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -125,3 +125,174 @@ FROM (SELECT '-Infinity'::float8 AS "float8field") q; -- json input SELECT row_to_json(q) FROM (SELECT '{"a":1,"b": [2,3,4,"d","e","f"],"c":{"p":1,"q":2}}'::json AS "jsonfield") q; + + +-- json extraction functions + +CREATE TEMP TABLE test_json ( + json_type text, + test_json json +); + +INSERT INTO test_json VALUES +('scalar','"a scalar"'), +('array','["zero", "one","two",null,"four","five"]'), +('object','{"field1":"val1","field2":"val2","field3":null}'); + +SELECT test_json -> 'x' +FROM test_json +WHERE json_type = 'scalar'; + +SELECT test_json -> 'x' +FROM test_json +WHERE json_type = 'array'; + +SELECT test_json -> 'x' +FROM test_json +WHERE json_type = 'object'; + +SELECT test_json->'field2' +FROM test_json +WHERE json_type = 'object'; + +SELECT test_json->>'field2' +FROM test_json +WHERE json_type = 'object'; + +SELECT test_json -> 2 +FROM test_json +WHERE json_type = 'scalar'; + +SELECT test_json -> 2 +FROM test_json +WHERE json_type = 'array'; + +SELECT test_json -> 2 +FROM test_json +WHERE json_type = 'object'; + +SELECT test_json->>2 +FROM test_json +WHERE json_type = 'array'; + +SELECT json_object_keys(test_json) +FROM test_json +WHERE json_type = 'scalar'; + +SELECT json_object_keys(test_json) +FROM test_json +WHERE json_type = 'array'; + +SELECT json_object_keys(test_json) +FROM test_json +WHERE json_type = 'object'; + +-- nulls + +select (test_json->'field3') is null as expect_false +from test_json +where json_type = 'object'; + +select (test_json->>'field3') is null as expect_true +from test_json +where json_type = 'object'; + +select (test_json->3) is null as expect_false +from test_json +where json_type = 'array'; + +select (test_json->>3) is null as expect_true +from test_json +where json_type = 'array'; + + +-- array length + +SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'); + +SELECT json_array_length('[]'); + +SELECT json_array_length('{"f1":1,"f2":[5,6]}'); + +SELECT json_array_length('4'); + +-- each + +select json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}'); +select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; + +select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}'); +select * from json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q; + +-- extract_path, extract_path_as_text + +select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); +select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); +select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); +select json_extract_path('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); +select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f4','f6'); +select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','f2'); +select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',0::text); +select json_extract_path_text('{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}','f2',1::text); + +-- extract_path nulls + +select json_extract_path('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_false; +select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":null,"f6":"stringy"}}','f4','f5') is null as expect_true; +select json_extract_path('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_false; +select json_extract_path_text('{"f2":{"f3":1},"f4":[0,1,2,null]}','f4','3') is null as expect_true; + +-- extract_path operators + +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6']; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2']; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','0']; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','1']; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f4','f6']; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2']; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','0']; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1']; + +-- same using array literals +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}'; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2}'; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,0}'; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,1}'; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f4,f6}'; +select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2}'; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}'; +select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}'; + +-- array_elements + +select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]'); +select * from json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]') q; + +-- populate_record +create type jpop as (a text, b int, c timestamp); + +select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; +select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; + +select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q; +select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q; + +select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q; +select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q; +select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q; + +-- populate_recordset + +select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; +select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; + +-- using the default use_json_as_text argument + +select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; +select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; |