tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.39k stars 359 forks source link

`Model.filter(column__filter={...})` on nested json value not working as excepted #1480

Open elder-frog opened 9 months ago

elder-frog commented 9 months ago

I hava a JSONField with column name config with some data like

=============================================
id | config
=============================================
1  | {"account": ["a", "b"]}
---------------------------------------------
2  | {"account": ["b", "c"]}
=============================================

I want to build a SQL as

SELECT *
FROM config_table
WHERE config -> 'account' @> '["a"]'

this code working well in raw sql, and when I use tortoise doing this in these code, tortoise generate wrong sql code

print(ConfigTable.filter(config__filter={"account__contains": ["a"]}).as_query())

# output:
# SELECT "id","config" FROM "config_table" WHERE "config"->'account'->>'contains'=1

I read some tortoise source code, in json_functions.py#L19, seems not support query using contains in nested json?