Skip to content
This repository was archived by the owner on May 14, 2026. It is now read-only.
This repository was archived by the owner on May 14, 2026. It is now read-only.

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

Closed

Description

@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 {}

Activity

  1. added
    priority: p2Moderately-important priority. Fix may not be included in next release.
    type: bugError or flaw in code with unintended results or allowing sub-optimal usage patterns.
    on Jun 23, 2024
  2. forksumit commented on Jun 23, 2024

    @forksumit
    Author

    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

  3. forksumit commented on Aug 13, 2024

    @forksumit
    Author
  4. added 2 commits that reference this issue on Dec 3, 2024
    058681f
    7952799
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

api: spannerIssues related to the googleapis/python-spanner-sqlalchemy API.priority: p2Moderately-important priority. Fix may not be included in next release.type: bugError or flaw in code with unintended results or allowing sub-optimal usage patterns.

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions