„Strawberry JSON Fields Forever“: Filtern nach JSON-Feldern mit GraphQL und Strawberry

1CREATE TABLE "Members"
2(
3    beatle_id          serial CONSTRAINT beatle_id_pk PRIMARY KEY,
4    name               VARCHAR,
5    age                INTEGER,
6    custom_information jsonb
7);
8 
9INSERT INTO public."Members" VALUES (1, 'John Lennon', 82, '{ "favoriteNumber": 3, "height": 182 }');
10INSERT INTO public."Members" VALUES (2, 'Paul McCartney', 80, '{ "height": 180 }');
11INSERT INTO public."Members" VALUES (3, 'George Harrison', 79, '{ "height": 181 }');
12INSERT INTO public."Members" VALUES (4, 'Ringo Starr', 82, '{ "height": 172 }');

1from fastapi import FastAPI
2from fastapi_sqlalchemy import DBSessionMiddleware
3 
4 
5app = FastAPI()
6app.add_middleware(DBSessionMiddleware, db_url="postgresql://postgres:postgres@localhost:5432/postgres")

1from sqlalchemy.ext.declarative import declarative_base
2from sqlalchemy import Column, Integer, String, JSON
3 
4 
5Base = declarative_base()
6 
7 
8class MemberModel(Base):
9    __tablename__ = "Members"
10 
11    beatle_id = Column(Integer, primary_key=True, index=True)
12    name = Column(String)
13    age = Column(Integer)
14    custom_information = Column(JSON)

1from typing import Optional
2 
3import strawberry
4from fastapi_sqlalchemy import db
5from sqlalchemy import text
6from strawberry.scalars import JSON
7 
8from app.models import MemberModel
9 
10 
11@strawberry.input
12class Filters:
13    custom_field: Optional[str] = None
14    custom_value: Optional[int] = None
15 
16 
17@strawberry.type
18class Member:
19    beatle_id: int
20    name: str
21    age: int
22    custom_information: JSON
23 
24    @classmethod
25    def marshal(cls, model: MemberModel) -> "Member":
26        return Member(beatle_id=strawberry.ID(model.beatle_id),
27                      name=model.name,
28                      age=model.age,
29                      custom_information=model.custom_information)
30 
31 
32@strawberry.type
33class Query:
34 
35    @strawberry.field(name="members")
36    def resolve_members(self, custom_information_filter: Filters = None) -> list[Member]:
37        if custom_information_filter:
38            field = custom_information_filter.custom_field
39            value = custom_information_filter.custom_value
40            members = db.session \
41                .query(MemberModel)\
42                .filter(text(f"CAST(custom_information->'{field}' AS INTEGER) = {value}"))\
43                .all()
44        else:
45            members = db.session.query(MemberModel).all()
46        return [Member.marshal(m) for m in members]
47 
48 
49schema = strawberry.Schema(Query)

1from fastapi import FastAPI
2from fastapi_sqlalchemy import DBSessionMiddleware  # middleware helper
3from strawberry.fastapi import GraphQLRouter
4 
5from app.schema import schema
6 
7 
8app = FastAPI()
9app.add_middleware(DBSessionMiddleware, db_url="postgresql://postgres:postgres@localhost:5432/postgres")
10app.include_router(GraphQLRouter(schema), prefix="/graphql")

1@strawberry.input
2class Filters:
3    custom_field: Optional[str] = None
4    custom_value: Optional[int] = None

1@strawberry.type
2class Member:
3    id: int
4    name: str
5    age: int
6    custom_information: JSON
7 
8    @classmethod
9    def marshal(cls, model: MemberModel) -> "Member":
10        return Member(id=strawberry.ID(model.beatle_id),
11                      name=model.name,
12                      age=model.age,
13                      custom_information=model.custom_information)

1@strawberry.type
2class Query:
3 
4    @strawberry.field(name="members")
5    def resolve_members(self, custom_information_filter: Filters = None) -> list[Member]:
6        if custom_information_filter:
7            field = custom_information_filter.custom_field
8            value = custom_information_filter.custom_value
9            members = db.session \
10                .query(MemberModel)\
11                .filter(text(f"CAST(custom_information->'{field}' AS INTEGER) = {value}"))\
12                .all()
13        else:
14            members = db.session.query(MemberModel).all()
15        return [Member.marshal(m) for m in members]