Support mode function for Google BigQuery

[GVerg]

Use case

We use JOOQ both for Postgres and BigQuery. The mode function is unfortunately unsupported for BigQuery, which makes the workaround complicated to implement and support.

Possible solution

No response

Possible workarounds

Creating an UDF.

jOOQ Version

3.20.0

Database product and version

Google BigQuery 2.25.0

Java Version

java 17.0.12

JDBC / R2DBC driver name and version (include name if unofficial driver)

No response

[lukaseder]

Thanks for your suggestion.

I don't see how MODE() can be emulated exactly. Using (APPROX_TOP_COUNT(x, 1))[0].value, an approximation is possible, but I'm not sure if this is desirable, as users might expect exact results. E.g. try this:

SELECT (APPROX_TOP_COUNT(x, 1))[0].value as mode
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

It yields: pear.

See also: https://cloud.google.com/bigquery/docs/reference/standard-sql/approximate_aggregate_functions#approx_top_count

[lukaseder]

Note, you can always use a plain SQL template to access APPROX_TOP_COUNT: https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/