英文:
How to get values only from the 1st level of JSON text in SQLAlchemy?
问题
filtered_query = session.query(Citys).filter(
~func.json_extract(Citys.metadatas, '$.apps').cast(String).contains('message')).all()
英文:
I have a table like this
class City(Base):
__tablename__ = 'Citys'
id = Column(Integer, primary_key=True)
name = Column(String)
metadatas = Column(String)
with these values
id=1, name="London", metadatas='{"lat":51.5072, "lon":0.1276, "Mayor": "Sadiq Khan", "participating":true, "apps":{"Facebook": {"messenger": true}, "Instagram":{"message":false}}}',
id=2, name="Liverpool", metadatas='{"lat":53.4084, "lon":2.9916, "Mayor": "Joanne Anderson", "participating":false, "apps":{"Twitter": {"messenger": true}, "telegram":{"message":false}}}',
id=3, name="Manchester", metadatas='{"lat":53.4808, "lon":2.2426, "Mayor": "Donna Ludford", "participating":true, "apps":{"TikTok": {"messenger": true}, "Google Meet":{"message":false}}}')]
the column metadatas is in json text format.
I want to implement a functionality that when a user searches for the app name it should return the id, name and metadatas related to it that filter app name.
for example if my search query is Facebook then it should return something like this
1 London {"lat":51.5072, "lon":0.1276, "Mayor": "Sadiq Khan", "participating":true, "apps":{"Facebook": {"messenger": true}, "Instagram":{"message":false}}}
if the user searches for something like message it shouldn't return anything.
Here's what I have implemented
filtered_query = session.query(Citys).filter(
func.json_extract(Citys.metadatas, '$.apps').cast(String).like('%' + 'Facebook' + '%')).all()
and this returns me
1 London {"lat":51.5072, "lon":0.1276, "Mayor": "Sadiq Khan", "participating":true, "apps":{"Facebook": {"messenger": true}, "Instagram":{"message":false}}}
but when I search for message it returns
1 London {"lat":51.5072, "lon":0.1276, "Mayor": "Sadiq Khan", "participating":true, "apps":{"Facebook": {"messenger": true}, "Instagram":{"message":false}}}
2 Liverpool {"lat":53.4084, "lon":2.9916, "Mayor": "Joanne Anderson", "participating":false, "apps":{"vonage": {"messenger": true}, "telegram":{"message":false}}}
3 Manchester {"lat":53.4808, "lon":2.2426, "Mayor": "Donna Ludford", "participating":true, "apps":{"TikTok": {"messenger": true}, "Meet":{"message":false}}}
which is not my desired result for the second search query it should return empty data
答案1
得分: 1
正如我提到的，你可以使用 [`json_contains_path`](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains-path) 如下所示：
with Session(engine) as session:
search_key = "Facebook"
filtered_query = (
session.query(City)
.filter(
func.json_contains_path(City.metadatas, "one", f'$.apps."{search_key}"')
)
.all()
)
for i in filtered_query:
print(i.name)
输出结果
`London`
如果我将 `search_key` 更改为 `message`，如预期那样，我什么都得不到。
英文:
As I mentioned you can use json_contains_path like so
with Session(engine) as session:
search_key = "Facebook"
filtered_query = (
session.query(City)
.filter(
func.json_contains_path(City.metadatas, "one", f'$.apps."{search_key}"')
)
.all()
)
for i in filtered_query:
print(i.name)
Output
London
If I change search_key to message, as expected, I get nothing.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论