sql-tests/inputs/json-functions.sql

0001 -- to_json
0002 select to_json(named_struct('a', 1, 'b', 2));
0003 select to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
0004 select to_json(array(named_struct('a', 1, 'b', 2)));
0005 select to_json(map(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 2)));
0006 select to_json(map('a', named_struct('a', 1, 'b', 2)));
0007 select to_json(map('a', 1));
0008 select to_json(array(map('a',1)));
0009 select to_json(array(map('a',1), map('b',2)));
0010 -- Check if errors handled
0011 select to_json(named_struct('a', 1, 'b', 2), named_struct('mode', 'PERMISSIVE'));
0012 select to_json(named_struct('a', 1, 'b', 2), map('mode', 1));
0013 select to_json();
0014
0015 -- from_json
0016 select from_json('{"a":1}', 'a INT');
0017 select from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
0018 -- Check if errors handled
0019 select from_json('{"a":1}', 1);
0020 select from_json('{"a":1}', 'a InvalidType');
0021 select from_json('{"a":1}', 'a INT', named_struct('mode', 'PERMISSIVE'));
0022 select from_json('{"a":1}', 'a INT', map('mode', 1));
0023 select from_json();
0024 -- json_tuple
0025 SELECT json_tuple('{"a" : 1, "b" : 2}', CAST(NULL AS STRING), 'b', CAST(NULL AS STRING), 'a');
0026 CREATE TEMPORARY VIEW jsonTable(jsonField, a) AS SELECT * FROM VALUES ('{"a": 1, "b": 2}', 'a');
0027 SELECT json_tuple(jsonField, 'b', CAST(NULL AS STRING), a) FROM jsonTable;
0028 -- Clean up
0029 DROP VIEW IF EXISTS jsonTable;
0030
0031 -- from_json - complex types
0032 select from_json('{"a":1, "b":2}', 'map<string, int>');
0033 select from_json('{"a":1, "b":"2"}', 'struct<a:int,b:string>');
0034
0035 -- infer schema of json literal
0036 select schema_of_json('{"c1":0, "c2":[1]}');
0037 select from_json('{"c1":[1, 2, 3]}', schema_of_json('{"c1":[0]}'));
0038
0039 -- from_json - array type
0040 select from_json('[1, 2, 3]', 'array<int>');
0041 select from_json('[1, "2", 3]', 'array<int>');
0042 select from_json('[1, 2, null]', 'array<int>');
0043
0044 select from_json('[{"a": 1}, {"a":2}]', 'array<struct<a:int>>');
0045 select from_json('{"a": 1}', 'array<struct<a:int>>');
0046 select from_json('[null, {"a":2}]', 'array<struct<a:int>>');
0047
0048 select from_json('[{"a": 1}, {"b":2}]', 'array<map<string,int>>');
0049 select from_json('[{"a": 1}, 2]', 'array<map<string,int>>');
0050
0051 -- from_json - datetime type
0052 select from_json('{"d": "2012-12-15", "t": "2012-12-15 15:15:15"}', 'd date, t timestamp');
0053 select from_json(
0054   '{"d": "12/15 2012", "t": "12/15 2012 15:15:15"}',
0055   'd date, t timestamp',
0056   map('dateFormat', 'MM/dd yyyy', 'timestampFormat', 'MM/dd yyyy HH:mm:ss'));
0057 select from_json(
0058   '{"d": "02-29"}',
0059   'd date',
0060   map('dateFormat', 'MM-dd'));
0061 select from_json(
0062   '{"t": "02-29"}',
0063   't timestamp',
0064   map('timestampFormat', 'MM-dd'));
0065
0066 -- to_json - array type
0067 select to_json(array('1', '2', '3'));
0068 select to_json(array(array(1, 2, 3), array(4)));
0069
0070 -- infer schema of json literal using options
0071 select schema_of_json('{"c1":1}', map('primitivesAsString', 'true'));
0072 select schema_of_json('{"c1":01, "c2":0.1}', map('allowNumericLeadingZeros', 'true', 'prefersDecimal', 'true'));
0073 select schema_of_json(null);
0074 CREATE TEMPORARY VIEW jsonTable(jsonField, a) AS SELECT * FROM VALUES ('{"a": 1, "b": 2}', 'a');
0075 SELECT schema_of_json(jsonField) FROM jsonTable;
0076 -- Clean up
0077 DROP VIEW IF EXISTS jsonTable;