feat: add method to_json() (at least for duckdb backend)

[cboettig]

Is your feature request related to a problem?

No response

What is the motivation behind your request?

No response

Describe the solution you'd like

Duckdb supports https://duckdb.org/docs/guides/file_formats/json_export.html, including support for JSON line format or ARRAY format.

native export methods are particularly handy since they can stream data too big for RAM, and can easily stream to remote sources like S3 buckets. It would be great to see this included among the other .to_* methods supported by duckdb.

I could take a go at a PR perhaps if you gave me a few pointers? but might be faster to roll this without me.

What version of ibis are you running?

9.5.0

What backend(s) are you using, if any?

DuckDB

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

This seems entirely reasonable. Not sure what other backends support writing JSON, but it's valuable enough for DuckDB and generically useful enough to have it available as an API IMO.

[cpcloud]

Since this is a feature addition, I'm going to remove it from the 10.0 milestone in the interest of trying to get a release out in January 2025.

[cboettig]

sounds good. Just noting the my current work-around to this (because somehow I always forget this)

expr = con.read_parquet(...).filter(...)
query = ibis.to_sql(expr)
con.raw_sql(f"COPY ({query}) TO 's3://{bucket}/{path}/example.json' (FORMAT JSON, ARRAY true);")