JSON_VALUE does not seem to accept dynamic SQL

[Vektrat]

What happened?

I've written a small client to publish UDFs from a SQL file to BigQuery, I've tried to do so in Golang and Python, with the same erroneous results (the error is coming from the server, bigquery_emulator).

The code is working in BigQuery studio, and even when uploading it to BigQuery via official GCP tooling, so unless I'm wrong somewhere I believe the issue may lay at the emulator's server side.

The failing part of the simplified code is this (note that I'm registering the function as an UDF to GCP, not executing it):

WHEN JSON_VALUE(param_1, param_2) IS NOT NULL THEN...

With the error:

big-query-testing > Traceback (most recent call last):
big-query-testing >   File "/gcl-builds/bigquery/XXX/testing/XXX_tests.py", line 50, in <module>
big-query-testing >     rows = query_job.result()   # waits for query to finish
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/job/query.py", line 1681, in result
big-query-testing >     while not is_job_done():
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 293, in retry_wrapped_func
big-query-testing >     return retry_target(
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 153, in retry_target
big-query-testing >     _retry_error_helper(
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_base.py", line 212, in _retry_error_helper
big-query-testing >     raise final_exc from source_exc
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 144, in retry_target
big-query-testing >     result = target()
big-query-testing >   File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/job/query.py", line 1630, in is_job_done
big-query-testing >     raise job_failed_exception
big-query-testing > google.api_core.exceptions.InternalServerError: 500 failed to analyze: INVALID_ARGUMENT: JSONPath must be a string literal or query parameter [at 7:18]; message: failed to analyze: INVALID_ARGUMENT: JSONPath must be a string literal or query parameter [at 7:18], reason: jobInternalError

However, this works:

WHEN JSON_VALUE(param_1, "any text") IS NOT NULL THEN...

What did you expect to happen?

I'd expect for the bigquery_emulator to accept this JSON function nomenclature just like BigQuery studio and GCP tools are.

How can we reproduce it (as minimally and precisely as possible)?

Failing code example:

CREATE OR REPLACE FUNCTION `project.dataset.ffff`(product_json STRING, product_name STRING) RETURNS STRING AS ((
    WITH whatever AS (
    SELECT '{"product_id": "101", "product_name": "Widget", "price": 9.99}' AS product_json
)

    SELECT 
        JSON_VALUE(product_json, product_name)
    FROM 
        whatever
));

Working code example:

CREATE OR REPLACE FUNCTION `project.dataset.ffff`(product_json STRING, product_name STRING) RETURNS STRING AS ((
    WITH whatever AS (
    SELECT '{"product_id": "101", "product_name": "Widget", "price": 9.99}' AS product_json
)

    SELECT 
        JSON_VALUE(product_json, '$.product_name')
    FROM 
        whatever
));

Anything else we need to know?

No response