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

[apoorva-01]

.as_string() is the right call here, not a workaround. It's the documented SQLAlchemy way and your original attempt is doing something subtly different than you'd expect.

When you write Foo.meta["hello"], the indexed access gives you back a value that's still typed as JSON, not as text. So Foo.meta["hello"] == "world" is a JSON-to-JSON comparison. On SQLite that renders as JSON_QUOTE(JSON_EXTRACT(meta, '$."hello"')), and JSON_QUOTE wraps the value in quotes, so you're really comparing the string "world" (quotes included) against your bare 'world'. They never match, which is why you get None.

.as_string() tells SQLAlchemy to pull the element out as text instead of JSON, so it drops the JSON_QUOTE and emits plain JSON_EXTRACT(...). That's exactly the difference you spotted in your two raw SQL versions.

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

There's a matching accessor for each type when you need it: .as_integer(), .as_boolean(), .as_float(), .as_json(). So Foo.meta["foo"]["bar"].as_integer() == 42 for your nested value.

So nothing's broken, and the docs do cover it, just tucked into the JSON type section rather than the SQLModel guide. The type-cast accessors are the intended API precisely because a raw col["key"] has to stay JSON so you can keep chaining into it.