查询子对象中返回的重复项 [英] Duplicates returned in query on child object

问题描述

我的文档中有一个这样的数据结构(请注意，为简便起见，将其简化):

I have a data structure in my document like this (note this is simplified for brevity):

{
    "id": "c1c1c1c1-c1c1-c1c1-c1c1-c1c1c1c1c1c1",
    "name": "Bruce Banner",
    "accountId": "a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1",
    "contributors": [{
        "accountId": "a2a2a2a2-a2a2-a2a2-a2a2-a2a2a2a2a2a2",
        "type": "Foo"
    },{
        "accountId": "a3a3a3a3-a3a3-a3a3-a3a3-a3a3a3a3a3a3",
        "type": "Bar"
    }]
},
{
    "id": "c2c2c2c2-c2c2-c2c2-c2c2-c2c2c2c2c2c2",
    "name": "Tony Stark",
    "accountId": "a2a2a2a2-a2a2-a2a2-a2a2-a2a2a2a2a2a2",
    "contributors": [{
        "accountId": "a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1",
        "type": "Fizz"
    }]
},

我正尝试编写一个查询，以检索提供的accountId在父记录上或在contributors数组内的文档:

I am attempting to write a query which retrieves documents where the provided accountId is either on the parent record or within the contributors array:

SELECT e.id, e.accountId, e.name
FROM Entitity e
JOIN co IN e.contributors
WHERE e.accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
OR co.accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'

结果:

[{
    "id": "c1c1c1c1-c1c1-c1c1-c1c1-c1c1c1c1c1c1",
    "accountId": "a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1",
    "name": "Bruce Banner"
},{
    "id": "c1c1c1c1-c1c1-c1c1-c1c1-c1c1c1c1c1c1",
    "accountId": "a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1",
    "name": "Bruce Banner"
},{
    "id": "c2c2c2c2-c2c2-c2c2-c2c2-c2c2c2c2c2c2",
    "accountId": "a2a2a2a2-a2a2-a2a2-a2a2-a2a2a2a2a2a2",
    "name": "Tony Stark"
}]

如您所见，第一个实体(Bruce Banner)是重复的.如果删除JOIN子句，它将正常工作.谁能告诉我这是为什么，以及如何避免重复?

As you can see, the first entity (Bruce Banner) is duplicated. If I remove the JOIN clause it works correctly. Can anyone tell me why this is, and how I can avoid the duplication?

编辑-为清楚起见，这是我的预期答复:

Edit - For clarity, this is my expected response:

[{
    "id": "c1c1c1c1-c1c1-c1c1-c1c1-c1c1c1c1c1c1",
    "accountId": "a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1",
    "name": "Bruce Banner"
},{
    "id": "c2c2c2c2-c2c2-c2c2-c2c2-c2c2c2c2c2c2",
    "accountId": "a2a2a2a2-a2a2-a2a2-a2a2-a2a2a2a2a2a2",
    "name": "Tony Stark"
}]

推荐答案

我看到您正在尝试查询accountId或contributors的accountId是否等于某个值.

I see you are trying to query on whether the accountId or the contributors's accountId is equal to some value.

今天-您需要使用JOIN运算符执行叉积运算以查询JSON数组中的所有元素(注意:您不需要JOIN即可查询特定的数组索引，例如WHERE e.contributors[0].accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1').

Today - you need to use the JOIN operator to perform a cross-product in order to query across all elements within a JSON array (note: you do not need a JOIN to query on a specific array index, e.g. WHERE e.contributors[0].accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1').

在您的示例abpve中，您将不可避免地从查询中获得重复项.您将需要实现一些应用程序逻辑，以从查询结果中过滤出重复项.

In your example abpve, you will get duplicates from the query which is unavoidable. You will want to implement some application logic to filter duplicates out of the query result.

要更好地了解JOIN的行为(认为简单的叉积)，请尝试从您要生成叉积的数组中添加一个字段(例如co.type):

To get a better picture in to the behavior of JOIN (think simple cross product), try adding a field from the array you are producing a cross product with (e.g. co.type):

SELECT e.id, e.accountId, e.name, co.type
FROM Entitity e
JOIN co IN e.contributors
WHERE e.accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
OR co.accountId = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'

其结果是:

[{
    id: c1c1c1c1 - c1c1 - c1c1 - c1c1 - c1c1c1c1c1c1,
    accountId: a1a1a1a1 - a1a1 - a1a1 - a1a1 - a1a1a1a1a1a1,
    name: Bruce Banner,
    type: Foo
}, {
    id: c1c1c1c1 - c1c1 - c1c1 - c1c1 - c1c1c1c1c1c1,
    accountId: a1a1a1a1 - a1a1 - a1a1 - a1a1 - a1a1a1a1a1a1,
    name: Bruce Banner,
    type: Bar
}, {
    id: c2c2c2c2 - c2c2 - c2c2 - c2c2 - c2c2c2c2c2c2,
    accountId: a2a2a2a2 - a2a2 - a2a2 - a2a2 - a2a2a2a2a2a2,
    name: Tony Stark,
    type: Fizz
}]

从结果中可以看到-每个子代(Foo，Bar和Fizz)都将返回一条记录.那是因为每个数组元素都与指定的查询匹配.

As you can see from the results - a record is being returned for each of the children: Foo, Bar, and Fizz. That is because each of those array elements match the specified query.

这篇关于查询子对象中返回的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！