与数组中的多个值匹配的CosmosDB SQL查询 [英] CosmosDB SQL query that matches multiple values in an array
问题描述
我正在使用Cosmos DB,我想编写一个SQL查询,该查询将匹配数组中的多个值.详细说明,假设您具有以下集合:
I am working with Cosmos DB and I want to write a SQL query that will match multiple values in an array. To elaborate, imagine you have the following collection:
[
{
"id": "31d4c08b-ee59-4ede-b801-3cacaea38808",
"name": "Oliver Queen",
"occupations": [
{
"job_title": "Billionaire",
"job_satisfaction": "pretty good"
},
{
"job_title": "Green Arrow",
"job_satisfaction": "meh"
}
]
},
{
"id": "689bdc38-9849-4a11-b856-53f8628b76c9",
"name": "Bruce Wayne",
"occupations": [
{
"job_title": "Billionaire",
"job_satisfaction": "pretty good"
},
{
"job_title": "Batman",
"job_satisfaction": "I'm Batman"
}
]
},
{
"id": "d1d3609a-0067-47e4-b7ff-afc7ee1a0147",
"name": "Clarke Kent",
"occupations": [
{
"job_title": "Reporter",
"job_satisfaction": "average"
},
{
"job_title": "Superman",
"job_satisfaction": "not as good as Batman"
}
]
}
]
我想编写一个查询,该查询将返回所有职业为job_title为"Billionaire"和"Batman"的条目.为了清楚起见,结果必须同时具有job_titles.因此,在上面的集合中,它只应返回Bruce Wayne.
I want to write a query that will return all entries that have an occupation with the job_title of "Billionaire" and "Batman". Just to be clear the results must have BOTH job_titles. So in the above collection it should only return Bruce Wayne.
到目前为止,我已经尝试过:
So far I have tried:
SELECT c.id, c.name, c.occupations FROM c
WHERE ARRAY_CONTAINS(c.occupations, {'job_title': 'Billionaire' })
AND ARRAY_CONTAINS(c.occupations, {'job_title': 'Batman' })
和
SELECT c.id, c.name, c.occupations FROM c
WHERE c.occupations.job_title = 'Batman'
AND c.occupations.job_title = 'Billionaire'
两者均返回空结果.
预先感谢
推荐答案
您需要使用ARRAY_CONTAINS(array,search_value,is_partial_match = true),即查询为:
You need to use ARRAY_CONTAINS(array, search_value, is_partial_match = true), i.e. the query is:
SELECT c.id, c.name, c.occupations
FROM c
WHERE ARRAY_CONTAINS(c.occupations, {'job_title': 'Billionaire' }, true)
AND ARRAY_CONTAINS(c.occupations, {'job_title': 'Batman' }, true)
这篇关于与数组中的多个值匹配的CosmosDB SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!