CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL VARCHAR, ANOTHER VARCHAR);
INSERT INTO TEST (ID, COL, ANOTHER) VALUES (1, 's1', 'c1'),(1, 's2', 'c1'), (1, 's3', 'c1'), (2, 's1', 'c2'), (2,'s2','c2');
SELECT DISTINCT
ID
, ANOTHER
, ARRAY_AGG(DISTINCT COL) OVER(PARTITION BY ID) AS COLS
FROM TEST;
ID
ANOTHER
COLS
1
c1
[ "s1", "s2", "s3" ]
2
c2
[ "s1", "s2" ]
MCVE in fakesnow:
import fakesnow
import snowflake.connector
def main():
with fakesnow.patch():
conn = snowflake.connector.connect(database="X", schema="Y")
conn.execute_string("CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL VARCHAR, ANOTHER VARCHAR)")
conn.execute_string("INSERT INTO TEST (ID, COL, ANOTHER) VALUES (1, 's1', 'c1'),(1, 's2', 'c1'),(1, 's3', 'c1'),(2, 's1', 'c2'), (2,'s2','c2')")
conn.cursor().execute("""
SELECT DISTINCT
ID
, ANOTHER
, ARRAY_AGG(DISTINCT COL) OVER(PARTITION BY ID) AS COLS
FROM TEST;
""").fetchall()
if __name__ == "__main__":
main()
Error:
$ FAKESNOW_DEBUG=1 python3 standalone.py
CREATE OR REPLACE TEMPORARY TABLE TEST (ID BIGINT, COL TEXT, ANOTHER TEXT);
INSERT INTO TEST (ID, COL, ANOTHER) VALUES (1, 's1', 'c1'), (1, 's2', 'c1'), (1, 's3', 'c1'), (2, 's1', 'c2'), (2, 's2', 'c2');
SELECT DISTINCT ID, ANOTHER, TO_JSON(ARRAY_AGG(DISTINCT COL)) OVER (PARTITION BY ID) AS COLS FROM TEST;
Traceback (most recent call last):
[...]
raise snowflake.connector.errors.ProgrammingError(msg=msg, errno=2003, sqlstate="42S02") from None
snowflake.connector.errors.ProgrammingError: 002003 (42S02): Catalog Error: to_json is not an aggregate function
MCVE in Snowflake:
MCVE in fakesnow:
Error:
Version: