Generated BQ code using wrong function

[kevin-meyers]

Version Information

Server Version: V2.25

Environment

Docker version, local macbook.

What is the current behaviour?

I am using a relationship from a PSQL table to a BQ table. One of the columns in PSQL is an array type, and the generated BQ code uses JSON_VALUE. dataset is the array column:

SELECT 
`unnest1`.`__argument_id__` AS `__argument_id__`,
       `unnest1`.`reported_metric_id` AS `reported_metric_id`,
       `unnest1`.`dataset` AS `dataset`
FROM (SELECT CAST(JSON_VALUE(`json`, '$.__argument_id__') AS INT64) AS `__argument_id__`,
             CAST(JSON_VALUE(`json`, '$.reported_metric_id') AS INT64) AS `reported_metric_id`,
             CAST(JSON_VALUE(`json`, '$.dataset') AS STRING) AS `dataset`
      FROM UNNEST(JSON_QUERY_ARRAY(@param0)) AS `json`
) AS `unnest1`

What is the expected behaviour?

JSON_VALUE returns null for arrays, it should be JSON_QUERY:

SELECT 
`unnest1`.`__argument_id__` AS `__argument_id__`,
       `unnest1`.`reported_metric_id` AS `reported_metric_id`,
       `unnest1`.`dataset` AS `dataset`
FROM (SELECT CAST(JSON_VALUE(`json`, '$.__argument_id__') AS INT64) AS `__argument_id__`,
             CAST(JSON_VALUE(`json`, '$.reported_metric_id') AS INT64) AS `reported_metric_id`,
             CAST(JSON_QUERY(`json`, '$.dataset') AS STRING) AS `dataset`
      FROM UNNEST(JSON_QUERY_ARRAY(@param0)) AS `json`
) AS `unnest1`

How to reproduce the issue?

1. Create a BQ table, with an array_to_string column
2. Create a PSQL table with an array column
3. make a relationship from PSQL to BQ including that array column
4. Query them and check the generated sql

Screenshots or Screencast

Any possible solutions/workarounds you're aware of?

Add a generated column to the PSQL table that converts array to string, and use that in the relationship
NOTE for anyone who needs the workaround: You need an immutable function to use in the generated column, this will work: https://dba.stackexchange.com/questions/299039/optimize-query-matching-first-n-items-of-an-array

Keywords

JSON_QUERY
JSON_VALUE
array in relationship