search

GoogleCloudPlatform / zetasql-toolkit

The ZetaSQL Toolkit is a library that helps users use ZetaSQL Java API to perform SQL analysis for multiple query engines, including BigQuery and Cloud Spanner.
Apache License 2.0
39 stars 10 forks source link

Function not found: JSON_STRIP_NULLS #81

Closed dion-gdn closed 4 months ago

dion-gdn commented 4 months ago

Hi,

I got this error: com.google.zetasql.toolkit.AnalysisException: Function not found: JSON_STRIP_NULLS [at 47:22]

I think it was caused by invalid arguments: com.google.zetasql.io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Function not found: JSON_STRIP_NULLS [at 47:22]

Here's a sample of the query I used:

... // (omitted)
SELECT
  ...
  JSON_VALUE_ARRAY(JSON_STRIP_NULLS(PARSE_JSON(
    JSON_QUERY(payload,'$.FOO_BAR')))) AS foo_bar,
  ...

Where payload data type is STRING but contains JSON data. For some reason ZetaSQL throws INVALID_ARGUMENT. I did check the ZetaSQL C++ code but I don't understand what's wrong.

Any insights into what's causing the error? Thanks

ppaglilla commented 4 months ago

This happens because we were missing one language feature (FEATURE_JSON_MUTATOR_FUNCTIONS) that needed to be enabled in the BigQueryLanguageOptions. I added it in c331a1c7fbdab78e72631ffc9bb29b0604bc54fe, it will be released with version 0.5.3 in a few days.

In the meantime, here's a small code snippet you can use to manually enable the feature on previous versions. Be sure to do this before creating your BigQueryCatalog.

// Do this before creating your BigQueryCatalog
BigQueryLanguageOptions.get()
  .enableLanguageFeature(LanguageFeature.FEATURE_JSON_MUTATOR_FUNCTIONS);
ppaglilla commented 4 months ago

Edited my previous comment with a much easier workaround.