create table json2_table (
    ts timestamp time index,
    j  json2
) with (
    'append_mode' = 'true',
    'sst_format' = 'flat',
);

insert into json2_table (ts, j)
values (1, '{"a": {"b": 1}, "c": "s1", "d": [{"e": {"f": 0.1}}]}'),
       (2, '{"a": {"b": -2}, "c": "s2", "d": [{"e": {"f": 0.2}}]}');

admin flush_table('json2_table');

insert into json2_table (ts, j)
values (3, '{"a": {"b": 3}, "c": "s3"}');

insert into json2_table
values (4, '{"a": {"b": -4}, "d": [{"e": {"g": -0.4}}]}'),
       (5, '{"a": {}, "c": "s5"}'),
       (6, '{"c": "s6"}');

admin flush_table('json2_table');

admin compact_table('json2_table', 'swcs', '86400');

insert into json2_table
values (7, '{"a": {"b": "s7"}, "c": [1], "d": [{"e": {"g": -0.7}}]}'),
       (8, '{"a": {"b": 8}, "c": "s8"}');

admin flush_table('json2_table');

insert into json2_table
values (9, '{"a": {"x": true}, "c": "s9", "d": [{"e": {"g": -0.9}}]}'),
       (10, '{"a": {"b": 10}, "y": false}');

-- SQLNESS REPLACE (peers.*) REDACTED
explain select j.a.b from json2_table;

-- SQLNESS REPLACE (peers.*) REDACTED
explain select j.a.x::bool from json2_table;

select j.a.b from json2_table order by ts;

select j.a, j.a.x from json2_table order by ts;

select j.c, j.y from json2_table order by ts;

select j from json2_table order by ts;

select * from json2_table order by ts;

select count(*) from (select j from json2_table group by j);

select count(*) from (select distinct j from json2_table);

select ts, j from (select ts, j from json2_table) order by ts;

select json_get(j, '') from json2_table order by ts;

select json_get(j, '$') from json2_table order by ts;

select json_get(j, '.') from json2_table order by ts;

select json_get(j, '$.') from json2_table order by ts;

select j.a.b + 1 from json2_table order by ts;

select abs(j.a.b) from json2_table order by ts;

-- "j.c" is of type "String", "abs" is expected to be all "null"s.
select abs(j.c) from json2_table order by ts;

select j.d from json2_table order by ts;

drop table json2_table;

-- A JSON null in a dynamically typed field must remain SQL NULL when the
-- field is projected to the default string type, rather than becoming "null".
create table json2_variant_null (
    ts timestamp time index,
    j json2
) with (
    'append_mode' = 'true',
    'sst_format' = 'flat'
);

insert into json2_variant_null values
    (1, '{"payload":{"value":1}}'),
    (2, '{"payload":"text"}'),
    (3, '{"payload":null}');

select ts, j.payload, j.payload is null as payload_is_null
from json2_variant_null
order by ts;

admin flush_table('json2_variant_null');

select ts, j.payload, j.payload is null as payload_is_null
from json2_variant_null
order by ts;

drop table json2_variant_null;
