modules/sql-engine/src/integrationTest/sql/function/json/test_json.test

# name: test/sql/function/json/test_json.test
# description: Just simple test to check support of JSON functions
# group: [json]

query R
select JSON_EXISTS('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address[0].town')
----
true

query R
select JSON_VALUE('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address[0].town')
----
Paris

query R
select JSON_QUERY('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address')
----
[{"town":"Paris"},{"town":"London"}]

query R
SELECT JSON_OBJECT (KEY 'companyId' VALUE '1', KEY 'companyName' VALUE 'GRIDGAIN')
----
{"companyId":"1","companyName":"GRIDGAIN"}

query R
SELECT JSON_ARRAY ('test', 1, 4.2)
----
["test",1,4.2]

statement ok
create table t(id int primary key, name varchar, age int);

statement ok
insert into t values (0, 'Alex', 32), (1, 'Maria', 27);

query T
select json_object('Name': name, 'age': age) from t
----
{"age":32,"Name":"Alex"}
{"age":27,"Name":"Maria"}

query T
select json_array(name, age) from t
----
["Alex",32]
["Maria",27]

query T
select json_object('address': json_object('city': 'City 17'))
----
{"address":{"city":"City 17"}}

skipif ignite3
# https://issues.apache.org/jira/browse/IGNITE-22032 date during json construction is returned in caclite's internal form (number of days)
query T
select json_object('date': '2010-01-01'::DATE)
----
{"date":"2010-01-01"}

query T
select json_object('date': '2010-01-01'::DATE::VARCHAR)
----
{"date":"2010-01-01"}