Can't parse SQLite json_array_agg with ORDER BY clause

[rosszurowski]

Version

1.30.0

What happened?

I'm trying to use the json_array_agg function to return data from both tables in a single result using the query below. I'm finding that the combination of json_array_agg with an ORDER BY throws an error during sqlc generation, even though SQLite supports it. Removing the ORDER BY clause causes the compilation to work just fine.

Relevant log output

line x:x no viable alternative at input ';'
line x:x extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
# package queries
queries.sql:1:1: extraneous input '(' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
db.go:25: running "sqlc": exit status 1

Database schema

CREATE TABLE item (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE document (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES item(id)
);

SQL queries

SELECT
  item.*,
  COALESCE(
    JSON_GROUP_ARRAY(
      document.id ORDER BY document.created_at ASC
    ) FILTER (WHERE document.id IS NOT NULL),
    '[]'
  ) as document_ids
FROM item
LEFT JOIN document ON document.item_id = item.id
GROUP BY item.id

Configuration

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go