-- Copyright 2004-2023 H2 Group. Multiple-Licensed under the MPL 2.0,
-- and the EPL 1.0 (https://h2database.com/html/license.html).
-- Initial Developer: H2 Group
--

CREATE TABLE TEST(ID INT PRIMARY KEY, N VARCHAR, J JSON) AS VALUES
    (1, 'Ten', JSON '10'),
    (2, 'Null', NULL),
    (3, 'False', JSON 'false'),
    (4, 'False', JSON 'false');
> ok

SELECT JSON_ARRAYAGG(J NULL ON NULL) FROM TEST;
>> [10,null,false,false]

SELECT JSON_ARRAYAGG(J) FROM TEST;
>> [10,false,false]

SELECT JSON_ARRAYAGG(ALL J) FROM TEST;
>> [10,false,false]

SELECT JSON_ARRAYAGG(DISTINCT J) FROM TEST;
>> [10,false]

SELECT JSON_ARRAYAGG(J NULL ON NULL) FROM TEST;
>> [10,null,false,false]

SELECT JSON_ARRAYAGG(J ABSENT ON NULL) FROM TEST;
>> [10,false,false]

SELECT JSON_ARRAYAGG(J ORDER BY ID DESC NULL ON NULL) FROM TEST;
>> [false,false,null,10]

SELECT JSON_ARRAY(NULL NULL ON NULL);
>> [null]

EXPLAIN SELECT JSON_ARRAYAGG(J) FROM TEST;
>> SELECT JSON_ARRAYAGG("J") FROM "PUBLIC"."TEST" /* PUBLIC.TEST.tableScan */

EXPLAIN SELECT JSON_ARRAYAGG(J NULL ON NULL) FROM TEST;
>> SELECT JSON_ARRAYAGG("J" NULL ON NULL) FROM "PUBLIC"."TEST" /* PUBLIC.TEST.tableScan */

EXPLAIN SELECT JSON_ARRAYAGG(J ABSENT ON NULL) FROM TEST;
>> SELECT JSON_ARRAYAGG("J") FROM "PUBLIC"."TEST" /* PUBLIC.TEST.tableScan */

EXPLAIN SELECT JSON_ARRAYAGG(J FORMAT JSON ABSENT ON NULL) FROM TEST;
>> SELECT JSON_ARRAYAGG("J" FORMAT JSON) FROM "PUBLIC"."TEST" /* PUBLIC.TEST.tableScan */

EXPLAIN SELECT JSON_ARRAYAGG(DISTINCT J FORMAT JSON ORDER BY ID DESC ABSENT ON NULL) FROM TEST;
>> SELECT JSON_ARRAYAGG(DISTINCT "J" FORMAT JSON ORDER BY "ID" DESC) FROM "PUBLIC"."TEST" /* PUBLIC.TEST.tableScan */

DELETE FROM TEST WHERE J IS NOT NULL;
> update count: 3

SELECT JSON_ARRAYAGG(J) FROM TEST;
>> []

SELECT JSON_ARRAYAGG(J NULL ON NULL) FROM TEST;
>> [null]

DELETE FROM TEST;
> update count: 1

SELECT JSON_ARRAYAGG(J) FROM TEST;
>> null

DROP TABLE TEST;
> ok

EXPLAIN SELECT JSON_ARRAYAGG(A ORDER BY 'a') FROM (VALUES 1, 2) T(A);
>> SELECT JSON_ARRAYAGG("A") FROM (VALUES (1), (2)) "T"("A") /* table scan */
