英文:
Projecting a table in COSMOS and using it with "Not In" clause
问题
{
"translation": "Hi I want to write a COSMOS DB Query to retrieve all the ID's that are not in the given list (this is static list I want to project as a table). To make it easy to understand, I'm giving SQL Server Version of the query\n\n\nselect t.id from\n(\nselect 1 as id Union All\nselect 2 as id Union All\nselect 3 as id Union All\nselect 4 as id\n) t\nwhere t.id not in\n(\nselect c.Id from SomeTable c\n)\n\nSimilar to the above, I want to project data as t in COSMOS DB query and I want to check against the existing 'c' in COSMOS DB to find what are all the missing ID's. Struggling to find a good example query.\n\nProjected table (t) will have the data as\n[\n{"id" : "1"},\n{"id" : "2"},\n{"id" : "3"},\n{"id" : "4"}\n]\n\nCosmos Db table (c) will have this data\n[\n{"id" : "1"},\n{"id" : "2"}\n]\n\nI want the result to show what are the items/records missing in C that is present in t\n\nExpected output\n\n[\n{ "id" : "3",\n"id" : "4"\n}\n]\n\nRemember t is static data (projected and not in COSMOS), c is the table/data that is available in COSMOS"
}
英文:
Hi I want to write a COSMOS DB Query to retrieve all the ID's that are not in the given list (this is static list I want to project as a table). To make it easy to understand, I'm giving SQL Server Version of the query
select t.id from
(
select 1 as id Union All
select 2 as id Union All
select 3 as id Union All
select 4 as id
) t
where t.id not in
(
select c.Id from SomeTable c
)
Similar to the above, I want to project data as t in COSMOS DB query and I want to check against the existing "c" in COSMOS DB to find what are all the missing ID's. Struggling to find a good example query.
Projected table (t) will have the data as
[
{"id" : "1"},
{"id" : "2"},
{"id" : "3"},
{"id" : "4"}
]
Cosmos Db table (c) will have this data
[
{"id" : "1"},
{"id" : "2"}
]
I want the result to show what are the items/records missing in C that is present in t
Expected output
[
{ "id" : "3",
"id" : "4"
}
]
Remember t is static data (projected and not in COSMOS), c is the table/data that is available in COSMOS
答案1
得分: 1
根据您问题中的初始陈述，似乎您想要过滤掉在静态列表中不存在的记录/文档的ID。
然而，您发布的SQL Server查询示例将提供静态列表中不存在于Cosmos DB集合中的ID，即在查询中的NOT IN子句。Cosmos DB不支持跨集合查询、创建临时表、变量或UNION/UNION ALL语句。
如果您想实现初始陈述中提到的内容，即"我想编写一个COSMOS DB查询，以检索不在给定列表中的所有ID”，您可以通过将静态列表值传递给查询并使用NOT IN子句来实现。示例查询如下。
SELECT c.id FROM c WHERE c.id NOT IN ('1', '5', '6')
我已经创建了三个样本数据记录，其ID值为1、2、3。运行上述查询后，我得到的结果是2、3，排除了ID为1的记录，因为它属于静态列表，即('1', '5', '6')。
英文:
By reading the initial statement in your question, it seems that you want to filter ids of records/documents which are not present in a static list.
However, the SQL Server query example posted will give us ids from static list which are not there in Cosmos DB collection i.e. NOT IN clause in your query. Cosmos DB does not support cross collection queries, creating temporary tables, variables or UNION/UNION ALL statements.
If you want to achieve what you have mentioned in the initial statement "I want to write a COSMOS DB Query to retrieve all the ID's that are not in the given list". You can do so by passing static list values in the query and using the NOT IN clause.
Sample query as below.
SELECT c.id FROM c WHERE c.id NOT IN ('1', '5', '6')
I have created three records for sample data with id values 1,2,3. After running the above query I am getting the result as 2,3 which excludes the record with id 1, which is part of the static list i.e. ('1', '5', '6').
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论