Issue in the Spanner Python client when an array value is present at the top-level of a JSON column

[omarqouqas]

The Python Spanner client does not support deserialising a JSON column which contains an array as the top level element. Although the insertion works (when pre-serialising using json.loads), reading back the row fails, either by returning incorrect data, or by throwing an error.

What is the business impact you are facing:

Unability to deploy a new feature in production relying on array values as top-level elements in JSON columns.

Environment details

• Python version: python3.8
• google-cloud-spanner version: Python client google-cloud-spanner 3.13.0

Description of the workload (business logic, access pattern, use case):

Accessing JSON columns using the Python client fails

Steps to reproduce

1. https://github.com/flovouin/spanner-python-json-column#running-the-example
2. ValueError: dictionary update sequence element #0 has length 1; 2 is required
   JsonObject([{"myFirstObject": 1}])

Code example

https://github.com/flovouin/spanner-python-json-column

Stack trace

File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/streamed.py", line 145, in __iter__
    self._consume_next()
  File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/streamed.py", line 137, in _consume_next
    self._merge_values(values)
  File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/streamed.py", line 105, in _merge_values
    self._current_row.append(_parse_value_pb(value, field_types[index]))
  File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/_helpers.py", line 244, in _parse_value_pb
    return JsonObject.from_str(value_pb.string_value)
  File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/data_types.py", line 45, in from_str
    return cls(json.loads(str_repr))
  File "venv/lib/python3.8/site-packages/google/cloud/spanner_v1/data_types.py", line 30, in __init__
    super(JsonObject, self).__init__(*args, **kwargs)
ValueError: dictionary update sequence element #0 has length 1; 2 is required

[vi3k6i5]

@omarqouqas : I am able to reproduce this locally, will investigate a fix for the same and get back with updates.

[asthamohta]

Hi @omarqouqas, I am assuming by your input that the type for jsonColumn should be ARRAY rather than json as you are trying to input an array of Jsons(Let me know if this assumption is incorrect). If you change the type and input the value as following, the code should be able to read the value correctly

def write():
    def insert(transaction: Transaction):
        transaction.insert_or_update(
            TABLE_NAME,
            COLUMN_NAMES,
            [
                (
                    ROW_ID,
                    # Change in how value is inserted
                    [JsonObject({"someJson": 1, "yo": ""}), JsonObject({"otherValue": "👋", "number": 3})],
                ),
            ],
        )
    database.run_in_transaction(insert)

[zagfai]

I don't think it's a good idea to support this feature. To make the type simpler, it's better only support dict to JSON.

[flovouin]

@asthamohta The point of the example is to demonstrate that the Python client does not support reading some valid JSON stored and correctly handled by the Spanner database. Workarounds do exist (like the one you mentioned), but the Python client will still crash when performing some perfectly valid queries. Here's a shorter example:

This works when running the query using the gcloud CLI:

$ gcloud spanner databases execute-sql <YOUR_DB> --sql "SELECT JSON '[{\"someJson\": 1}]' AS ArrayCol"
ArrayCol
[{"someJson":1}]

The same query with the Python client crashes:

with database.snapshot() as snapshot:
        rows = snapshot.execute_sql("SELECT JSON '[{\"someJson\": 1}]' AS ArrayCol")
        print(list(rows))

    super(JsonObject, self).__init__(*args, **kwargs)
ValueError: dictionary update sequence element #0 has length 1; 2 is required

(You can basically run all the examples here without even writing the JSON data in a table, and you will get either a crash or an incorrect result.)

[asthamohta]

Hey @flovouin, you're right. I looked further into it and the following PR should fix it. We are just reviewing and testing it:#782
After this is merged I'll update the samples to showcase the correct use as well.