Improper deserialization of JSON type when top of level of data is array

[forksumit]

When inserting list<dict> (ex: [{"a": "b"}, {"x": "y"}]) into a JSON type via SQLAlchemy, insert works fine but when reading the same record, it gets deserialized to an empty JsonObject ({})

Environment details

• Programming language: Python
• OS: MacOS / Linux
• Language runtime version: 3.11.4
• Package version:
  • sqlalchemy-spanner==1.7.0
  • google-cloud-spanner==3.47.0
  • SQLAlchemy==2.0.30

Steps to reproduce

import sqlalchemy as sqla
from sqlalchemy.orm import Session, declarative_base

PROJECT_ID = "****"
INSTANCE_ID = "spanner-test"
DATABASE_ID = "sumit-poc"
DATABASE_URL = f"spanner+spanner:///projects/{PROJECT_ID}/instances/{INSTANCE_ID}/databases/{DATABASE_ID}"

engine = sqla.create_engine(DATABASE_URL)

metadata = sqla.MetaData()
ORMModelBase = declarative_base(metadata=metadata)


class SampleModel(ORMModelBase):
    __tablename__ = "SampleModel"

    id = sqla.Column("id", sqla.types.Integer, primary_key=True)
    data = sqla.Column("data", sqla.types.JSON, nullable=False)


if __name__ == "__main__":
    session = Session(engine)

    d = {"a": "b"}

    r = SampleModel(id=101, data=d)
    session.add(r)
    r = SampleModel(id=102, data=[d])
    session.add(r)
    session.commit()

    r1 = session.query(SampleModel).filter(SampleModel.id == 101).first()
    print("r1.data", r1.data)
    r2 = session.query(SampleModel).filter(SampleModel.id == 102).first()
    print("r2.data", r2.data)
    session.commit()

Expected Output:

r1.data {'a': 'b'}
r2.data [{'a': 'b'}]

Got Output:

r1.data {'a': 'b'}
r2.data {}

[forksumit]

Spanner lib returns a JsonObject when datatype is JSON, spanner dialect again tries to deserialize the same via JsonObject() which returns an improper object

Related issue: googleapis/python-spanner#1154

[forksumit]

Should be fixed by googleapis/python-spanner#1166