JSON values are not properly unmarshalled into SQLite values in `tabledata/insertAll`

[ohaibbq]

trafficstars

What happened?

The following Python BigQuery code illustrates a behavior where JSON_VALUE(json_column, "$.field") returns nil. This is because the json column is sent by the Python / Golang clients as a string, and end up in SQLite as a string.

    def test_load_json_and_json_value(self):
        address = BigQueryAddress(dataset_id=_DATASET_1, table_id=_TABLE_1)
        self.create_mock_table(
            address=address,
            schema=[
                bigquery.SchemaField(
                    "json_column",
                    field_type=bigquery.enums.StandardSqlTypeNames.JSON.value,
                    mode="NULLABLE",
                ),
            ],
        )
       # insert via google.cloud.bigquery.Client.insert_rows
        self.load_rows_into_table(
            address=address,
            data=[
                {"json_column": {"a_date": "2024-01-01", "b_float": 1.2}},
                {"json_column": {"a_date": "2025-01-01", "b_float": 5.6}},
            ],
        )
        test_query = f"""
        SELECT
            JSON_VALUE(json_column, "$.test_date") AS a_date,
            JSON_VALUE(json_column, "$.test_float") AS b_float,
        FROM `{self.project_id}.{address.dataset_id}.{address.table_id}`
        """
        self.run_query_test(
            test_query,
            expected_result=[
                {"a_date": "2024-01-01", "b_float": 1.2},
                {"a_date": "2025-01-01", "b_float": 5.6},
            ],
        )

What did you expect to happen?

Supported go-zetasqlite JSON functions should function as they do in its tests

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

linked testcase in pr

Anything else we need to know?

No response

[johanhaleby]

@ohaibbq Any workaround?

[ohaibbq]

@johanhaleby Not that I can recall. We have been using the fix from #335 in our fork.