How to SELECT using JSON for WHERE-clause

[brainbytes42]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

import uuid
from typing import Any
from uuid import UUID

from sqlalchemy import JSON, create_engine, text
from sqlalchemy.ext.mutable import MutableDict
from sqlmodel import Field, Session, SQLModel, select


class Foo(SQLModel, table=True):
    id: UUID = Field(primary_key=True, default_factory=uuid.uuid4)
    foo: str
    meta: dict[str, Any] = Field(sa_type=MutableDict.as_mutable(JSON))


engine = create_engine("sqlite:///:memory:", echo=True)


SQLModel.metadata.create_all(engine)


with Session(engine) as session:
    session.add(
        Foo(
            foo="Bar",
            meta={"hello": "world", "foo": {"bar": 42}},
        )
    )
    session.commit()

    # Ensure row is available
    foo = session.exec(select(Foo)).first()
    print(">>> first row:", foo)

    # Query by JSON-Content
    foo = session.exec(select(Foo).where(Foo.meta["hello"] == "world")).first()
    print(">>> select SQLModel:", foo)

    # Query like created by the expression above, doesn't work as well
    foo = session.execute(text("SELECT foo.id, foo.foo, foo.meta FROM foo "
                          "WHERE JSON_QUOTE(JSON_EXTRACT(foo.meta, '$.\"hello\"')) = 'world'")).first()
    print(">>> select text:", foo)

    # removed JSON_QUOTE - now it works!
    foo = session.execute(text("SELECT foo.id, foo.foo, foo.meta FROM foo "
                          "WHERE JSON_EXTRACT(foo.meta, '$.\"hello\"') = 'world'")).first()
    print(">>> select text without JSON_QUOTE:", foo)

Description

I want to use a JSON-Column in my SELECT's WHERE, but the select seems to find no result.

session.exec(select(Foo).where(Foo.meta["hello"] == "world")).first()    # => None

Although the JSON-Data is contained in that row:

session.exec(select(Foo)).first()      # => meta={'hello': 'world', 'foo': {'bar': 42}} id=UUID('3511f27f-266e-42e5-a200-e80a8abef4e9') foo='Bar'

the generated query seems kind of correct, althought the JSON_QUOTE seems to prevent it from working. if I remove this call, the query works as expected.

Another workaround i found, is to add as_string() to the select, but I don't know if this is the intended way - at least, it seems not to be documented anywhere...?

session.exec(select(Foo).where(Foo.meta["hello"].as_string() == "world")).first()

Thanks for clarification and maybe updating docs (or ideally fixing the code, so that my original attemt just works 😉).

Operating System

Windows

Operating System Details

No response

Project Version

0.0.39

Python Version

No response

Additional Context

No response