Deserializing JSON subfields within structs fails

[jonathan-ostrander]

Environment details

• OS type and version: MacOS Ventura 13.1
• Python version: 3.9.5
• pip version: 22.3.1
• google-cloud-bigquery version: 3.5.0

Steps to reproduce

1. Create table with record field containing a JSON subfield
2. Query record field and try to read row from query result

Code example

from google.cloud import bigquery
bigquery.Client().query("SELECT record_with_json_subfield.* FROM <table>").result().__next__()

Stack trace

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<stdin>", line 1, in <listcomp>
  File "lib/python3.9/site-packages/google/api_core/page_iterator.py", line 209, in _items_iter
    for item in page:
  File "lib/python3.9/site-packages/google/api_core/page_iterator.py", line 131, in __next__
    result = self._item_to_value(self._parent, item)
  File "lib/python3.9/site-packages/google/cloud/bigquery/table.py", line 2710, in _item_to_row
    _helpers._row_tuple_from_json(resource, iterator.schema),
  File "lib/python3.9/site-packages/google/cloud/bigquery/_helpers.py", line 435, in _row_tuple_from_json
    row_data.append(_field_from_json(cell["v"], field))
  File "lib/python3.9/site-packages/google/cloud/bigquery/_helpers.py", line 411, in _field_from_json
    return converter(resource, field)
  File "lib/python3.9/site-packages/google/cloud/bigquery/_helpers.py", line 368, in _record_from_json
    converter = _CELLDATA_FROM_JSON[subfield.field_type]
KeyError: 'JSON'

[jekozyra]

I just encountered the same issue on 2.34.4.

[chalmerlowe]

I ran this query using version 3.10.0 with no issues.

[1tux]

I believe it happens with nested jsons. I encountered it on version 3.10.0 as well.

[1tux]

Here is a hotfix that was working for me:

from google.cloud import bigquery

import json

def _json_from_json(value, _):
    """NOOP string -> string coercion"""
    return json.loads(value)


bigquery._helpers._CELLDATA_FROM_JSON['JSON'] = _json_from_json

[SergeyShk]

Could you apply this hotfix to the stable version? It seems like to work

[sirkozuch]

The issue is happening also on earlier versions of the SDK. Tested on 2.20.0 and 2.34.1.

[masahitojp]

I have also tried with 3.11.3. What I have tried is the following configuration fields

        - name: structarr
          type: STRUCT
          mode: repeated
          fields:
            - name: key
              type: STRING
            - name: value
              type: JSON

I tried running this hotfix. I got the data without any error!
#1500 (comment)

[vvkh]

@tswast still receiving the error:

$ pip list | grep bigquery
google-cloud-bigquery       3.14.1

  File "/home/datafold/.local/lib/python3.11/site-packages/google/cloud/bigquery/table.py", line 2962, in _item_to_row
    _helpers._row_tuple_from_json(resource, iterator.schema),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/datafold/.local/lib/python3.11/site-packages/google/cloud/bigquery/_helpers.py", line 300, in _row_tuple_from_json
    row_data.append(_field_from_json(cell["v"], field))
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/datafold/.local/lib/python3.11/site-packages/google/cloud/bigquery/_helpers.py", line 276, in _field_from_json
    return converter(resource, field)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/datafold/.local/lib/python3.11/site-packages/google/cloud/bigquery/_helpers.py", line 233, in _record_from_json
    converter = _CELLDATA_FROM_JSON[subfield.field_type]
                ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
KeyError: 'JSON'

Looks like JSON is still missing in _CELLDATA_FROM_JSON

I think #1742 should be merged as well.

[tswast]

#1742 has now been merged.

[tswast]

Note: Unfortunately, we're now in a holiday season release freeze, but you can test the change out with pip install --upgrade "git+https://github.com/googleapis/python-bigquery.git#egg=google-cloud-bigquery"

[colin-rogers-dbt]

@tswast when is the freeze over / is there a schedule for the next release?

[tswast]

@colin-rogers-dbt Thanks for the reminder. I don't see any blockers on #1752 so I'll cut the release now.