couchbase - 如何加入一个数组中的元素,该数组也是另一个数组的一部分,以在沙发库中获得最佳性能?
问题描述
以下是组织的示例文件
{
"org": {
"id": "org_2_1084",
"organizationId": 1084,
"organizationName": "ABC",
"organizationRoles": [
{
"addressAssociations": [
{
"activeDate": "2019-08-03T18:52:00.857Z",
"addressAssocTypeId": -2,
"addressId": 100,
"ownershipStatus": 1,
"srvAddressStatus": 1
},
{
"activeDate": "2019-08-03T18:52:00.857Z",
"addressAssocTypeId": -2,
"addressId": 105,
"ownershipStatus": 1,
"srvAddressStatus": 1
}
],
"name": "NLUZ",
"organizationRoleId": 893,
"roleSpecId": -104,
"statusId": 1,
"statusLastChangedDate": "2019-08-04T13:14:44.616Z"
},
{
"addressAssociations": [
{
"activeDate": "2019-08-03T18:52:00.857Z",
"addressAssocTypeId": -2,
"addressId": 582,
"ownershipStatus": 1,
"srvAddressStatus": 1
},
{
"activeDate": "2019-08-03T18:52:00.857Z",
"addressAssocTypeId": -2,
"addressId": 603,
"ownershipStatus": 1,
"srvAddressStatus": 1
}
],
"name": "TXR",
"organizationRoleId": 894,
"partyRoleAssocs": [
{
"partyRoleAssocId": "512"
}
],
"roleSpecId": -103,
"statusId": 1,
"statusLastChangedDate": "2019-08-04T13:14:44.616Z"
},
} 下面是地址的示例文档
{
"address": {
"address1": "string",
"address2": "string",
"addressId": "1531",
"changeWho": "string",
"city": "string",
"fxGeocode": "string",
"houseNumber": "string",
"id": "1531",
"isActive": true,
"postalCode": "string",
"state": "string",
"streetName": "string",
"tenantId": "2",
"type": "address",
"zip": "string"
}
}
一个组织中有多个organizationRole,一个organizationRole 中有多个addressAssociation。每个addressAssociation 包含一个addressId,与该addressId 对应的地址存储在address 文档中。
现在我必须从这两个文档中获取组织角色名称、组织角色 ID、城市、zip。为了在 couchbase 中获得最佳性能,处理这种情况的最佳方法应该是什么?
我正在考虑使用 join 但无法针对这种情况提出确切的查询。我已经尝试了以下查询,但它不起作用。
select *
from 'contact' As A UNNEST 'contact'.organizationRoles as Roles
UNNEST Roles.addressAssociations address
Join 'contact' As B
on address.addressID=B.addressID
where A.type="organization" and B.type="address";
解决方案
你是在正确的方向。
在addressAssociations 中addressId 是数字,在地址addressId 中是字符串。字符串和数字不相同,并且没有隐式类型转换。您必须使用 TOSTRING()、TONUMBER() 等修复数据或进行显式类型转换...
此外,N1QL 字段名称区分大小写,您的查询使用 addressID 与 addressId(在文档中)
SELECT r.name AS organizationRoleName, r.organizationRoleId, a.city, a.zip
FROM contact AS c
UNNEST c.organizationRoles AS r
UNNEST r.addressAssociations AS aa
jOIN contact AS a
ON aa.addressId = a.addressId
WHERE c.type = "organization" AND a.type = "address";
CREATE INDEX ix1 ON contact(addressId, city, zip) WHERE type = "address";
推荐阅读
- .net-core - 不能包含“布尔”项的脚手架(EF,Core 2.1)模型
- c# - Wpf 数据绑定参数问题
- multithreading - Python 3.6:将在单独的 CMD 会话中运行相同的多线程 python 脚本绕过 Python 的 GIL 问题
- linux - 判断当前线程是否在 Linux x86 上被中断
- javascript - pdf.js mozilla lib问题以正确的顺序获取或提取纯文本
- javascript - 用javascript按住鼠标时如何运行函数
- css - A :focus-within 解决方法
- c++ - 如何在 C++ 中使用 yaml-cpp 中的环境变量
- excel - Excel 突出显示单元格中与其他单元格不同的部分文本
- firebase - 将 Stream Building 与特定的 Firestore 文档一起使用