`parse_one` fails on valid BigQuery `JSON_OBJECT` with aggregated keys and values

[karakanb]

parse_one fails on a valid BigQuery JSON_OBJECT(ARRAY_AGG(...), ARRAY_AGG(...)) expression that sqlglot parses successfully.

sqlglot

# /// script
# requires-python = ">=3.11"
# dependencies = [
#   "sqlglot==29.0.1",
# ]
# ///

import textwrap

import sqlglot


QUERY = textwrap.dedent(
    """
    SELECT
      IF(
        ARRAY_LENGTH(experiments) > 0,
        (
          SELECT JSON_OBJECT(
            ARRAY_AGG(CAST(e.id AS STRING)),
            ARRAY_AGG(e.value)
          )
          FROM UNNEST(experiments) AS e
        ),
        JSON "{}"
      ) AS experiments
    FROM `events.events_json`
    """
).strip()


def main() -> None:
    print("case=json-object-bigquery")
    print("library=sqlglot")
    print("version=29.0.1")
    print("status=success")
    parsed = sqlglot.parse_one(QUERY, dialect="bigquery")
    print(f"normalized_sql={parsed.sql(dialect='bigquery')}")


if __name__ == "__main__":
    main()

polyglot repro

use polyglot_sql::{generate, parse_one, DialectType};

const QUERY: &str = r#"
SELECT
  IF(
    ARRAY_LENGTH(experiments) > 0,
    (
      SELECT JSON_OBJECT(
        ARRAY_AGG(CAST(e.id AS STRING)),
        ARRAY_AGG(e.value)
      )
      FROM UNNEST(experiments) AS e
    ),
    JSON "{}"
  ) AS experiments
FROM `events.events_json`
"#;

fn main() {
    println!("case=json-object-bigquery");
    println!("library=polyglot-sql");
    println!("version=0.2.2");

    match parse_one(QUERY, DialectType::BigQuery) {
        Ok(parsed) => {
            println!("status=success");
            match generate(&parsed, DialectType::BigQuery) {
                Ok(sql) => {
                    println!("normalized_sql={sql}");
                }
                Err(err) => {
                    println!("status=error");
                    println!("error={err}");
                    std::process::exit(1);
                }
            }
        }
        Err(err) => {
            println!("status=error");
            println!("error={err}");
            std::process::exit(1);
        }
    }
}

[tobilg]

Thanks for reporting, this will be fixed with the next version 0.2.3.