JSON_EXTRACT_SCALAR nor JSON_QUERY does not work as expected

[cdromas]

What happened?

Considering having a table named test in a dataset named data_history with a JSON field named data.

SELECT JSON_QUERY(data, '$.data.test') FROM data_history.test WHERE TIMESTAMP_TRUNC(date, DAY) = TIMESTAMP("2024-03-14") LIMIT 1

Return the entire JSON value

What did you expect to happen?

SELECT JSON_QUERY(data, '$.data.test') FROM data_history.test WHERE TIMESTAMP_TRUNC(date, DAY) = TIMESTAMP("2024-03-14") LIMIT 1

The result should be the extracted field test

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

{data:"{"test":"hello"}"}

Anything else we need to know?

No response

[szaffarano]

To reproduce the issue you can also use the bq client with the sample data as shown in the README

❯ bq --api http://0.0.0.0:9050 query --project_id=test 'SELECT structarr[OFFSET(0)].value.age FROM dataset1.table_a'

+-----------------------------------------+
|                   age                   |
+-----------------------------------------+
| {"age": 10, "nested": {"key": "value"}} |
| {"age": 15, "nested": {"key": "value"}} |
+-----------------------------------------+

❮ bq --api http://0.0.0.0:9050 query --project_id=test 'SELECT structarr[OFFSET(0)].value.nested FROM dataset1.table_a'

+-----------------------------------------+
|                 nested                  |
+-----------------------------------------+
| {"age": 10, "nested": {"key": "value"}} |
| {"age": 15, "nested": {"key": "value"}} |
+-----------------------------------------+

❯ bq --api http://0.0.0.0:9050 query --project_id=test 'SELECT structarr[OFFSET(0)].value.nested.key FROM dataset1.table_a'

+------+
| key  |
+------+
| NULL |
| NULL |
+------+

nested is an extra JSON key I added into server/testdata/data.yaml