对数组中嵌套对象的jsonb LIKE查询 [英] jsonb LIKE query on nested objects in an array
问题描述
我的JSON数据如下:
My JSON data looks like this:
[{
"id": 1,
"payload": {
"location": "NY",
"details": [{
"name": "cafe",
"cuisine": "mexican"
},
{
"name": "foody",
"cuisine": "italian"
}
]
}
}, {
"id": 2,
"payload": {
"location": "NY",
"details": [{
"name": "mbar",
"cuisine": "mexican"
},
{
"name": "fdy",
"cuisine": "italian"
}
]
}
}]
给定一个文本"foo",我想返回所有具有该子字符串的元组.但是我不知道如何编写相同的查询.
given a text "foo" I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same.
我关注了此相关答案,但无法弄清楚LIKE
的操作方法.
这就是我现在正在做的事情:
I followed this related answer but cannot figure out how to do LIKE
.
This is what I have working right now:
SELECT r.res->>'name' AS feature_name, d.details::text
FROM restaurants r
, LATERAL (SELECT ARRAY (
SELECT * FROM json_populate_recordset(null::foo, r.res#>'{payload,
details}')
)
) AS d(details)
WHERE d.details @> '{cafe}';
我不想传递cafe
的整个文本,而是传递ca
并获得与该文本匹配的结果.
Instead of passing the whole text of cafe
I want to pass ca
and get the results that match that text.
推荐答案
您的解决方案可以进一步简化:
Your solution can be simplified some more:
SELECT r.res->>'name' AS feature_name, d.name AS detail_name
FROM restaurants r
, jsonb_populate_recordset(null::foo, r.res #> '{payload, details}') d
WHERE d.name LIKE '%oh%';
或更简单,使用 jsonb_array_elements()
,因为在此示例中,您实际上根本不需要行类型(foo
):
SELECT r.res->>'name' AS feature_name, d->>'name' AS detail_name
FROM restaurants r
, jsonb_array_elements(r.res #> '{payload, details}') d
WHERE d->>'name' LIKE '%oh%';
dbfiddle 此处
但这完全是您要求的 不是 :
But that's not what you asked exactly:
我想返回所有具有该子字符串的元组.
I want to return all the tuples that have this substring.
您将返回所有JSON数组元素(每个基表行0-n),其中一个特定键('{payload,details,*,name}'
)匹配(区分大小写).
You are returning all JSON array elements (0-n per base table row), where one particular key ('{payload,details,*,name}'
) matches (case-sensitively).
您的原始问题在此之上有一个嵌套的JSON数组.您已为该解决方案移除了外部阵列-我做了同样的事情.
And your original question had a nested JSON array on top of this. You removed the outer array for this solution - I did the same.
根据您的实际要求,新文本Postgres 10的搜索功能可能会有用.
Depending on your actual requirements the new text search capability of Postgres 10 might be useful.
这篇关于对数组中嵌套对象的jsonb LIKE查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!