如何在cosmosdb sql中显示非不同或重复的记录

Question

我有一个cosmosdb集合，它的记录如下(我为示例显示了2条记录(：

[
{
"id": "0000001|0000000|0000002",
"primarycode": "0000001",
"code2": "0000000",
"code3": "0000002",
"array": [
{
"requiredvalue": "79050040002010",
"someobject": {
"abc": "02",
"def": "05",
"ghi": "04",
"jkl": "03"
}
}
]
},
{
"id": "0000001|0000003|0000002",
"primarycode": "0000001",
"code2": "0000003",
"code3": "0000002",
"array": [
{
"requiredvalue": "79050040002010",
"someobject": {
"abc": "02",
"def": "05",
"ghi": "04",
"jkl": "03"
}
},
{
"requiredvalue": "79050040005680",
"someobject": {
"abc": "02",
"def": "05",
"ghi": "04",
"jkl": "03"
}
}
]
}
]

我需要得到的是常见的"；所需值"；从这两个记录(其为"79050040002010"(中；primaryCode"；但是"；代码2"；不同，因此id也不同。

我能够得到明显的"；所需值"；从这些记录中查询：

SELECT DISTINCT f.requiredvalue FROM c JOIN f IN c.array WHERE c.primarycode="0000001"

这将返回"；所需值"；用"；79050040002010"79050040005680"；。但我只想要普通的"；所需值"；其为"；79050040002010"；。

Answer 1

您可以使用不久前才引入的GROUP BY子句。示例：

SELECT s.requiredvalue
FROM (
SELECT f.requiredvalue, COUNT(1) AS count
FROM c
JOIN f IN c.array1
WHERE c.primarycode='0000001'
GROUP BY f.requiredvalue
) s
WHERE s.count > 1

edit：这里也是一个查询，以获得每个主要代码的重叠要求值

SELECT s.primarycode, s.requiredvalue
FROM (
SELECT c.primarycode, f.requiredvalue, COUNT(1) AS count
FROM c
JOIN f IN c.array1
GROUP BY c.primarycode, f.requiredvalue
) s
WHERE s.count > 1