AQL查询优化：关联属性、服务、设备集合并生成数组格式输出

数据模型 #

erDiagram
    PROPERTIES ||--|{ APPLIANCES : contains 
    PROPERTIES ||--|{ SERVICES : provides

需求 #

需要通过单个AQL查询，输出包含对应services和appliances数组的属性数据，预期JSON格式如下：

{
    "name": "{{$randomNoun}}",
    "description": "{{$randomWords}}",
    "main_image": "{{$randomImageUrl}}",
    "completeness": 1,
    "short_code": {{$randomInt}},
    "location": {
        "name": "{{$randomNoun}}",
        "street": "{{$randomStreetAddress}}",
        "number": "{{$randomInt}}",
        "city": "{{$randomCity}}",
        "country": "{{$randomCountry}}",
        "postalCode": "{{$randomInt}}{{$randomInt}}{{$randomInt}}{{$randomInt}}{{$randomInt}}",
        "coordinates": [
            {{$randomLongitude}},
            {{$randomLatitude}}
        ],
        "type": "Point"
    },
    "procedures": [
        "no smoking,",
        "no pets",
        "no party"
    ],
    "services": [
        {
            "name": "wifi",
            "description": "free wifi",
            "price": 12.5,
            "price_unit": "group"
        }
    ],
    "appliances": [
        {
            "manufacturer": "{{$randomCompanyName}}",
            "model": "{{$randomBsNoun}}",
            "description": "{{$randomLoremSentence}}",
            "price": {{$randomInt}},
            "how_many": {{$randomInt}},
            "credentials": {
                "type": "basic",
                "username": "{{$randomUserName}}",
                "password": "{{$randomPassword}}"
            },
            "images": [
                "{{$randomAbstractImage}}",
                "{{$randomAbstractImage}}",
                "{{$randomAbstractImage}}"
            ],
            "usage_video": "https://www.youtube.com/watch?v=jfKfPfyJRdk",
            "user_manual_url": "{{$randomUrl}}",
            "user_manual_file": "{{$randomUrl}}"
        },
        {
            "manufacturer": "{{$randomCompanyName}}",
            "model": "{{$randomBsNoun}}",
            "description": "{{$randomLoremSentence}}",
            "price": {{$randomInt}},
            "how_many": {{$randomInt}},
            "credentials": {
                "type": "basic",
                "username": "{{$randomUserName}}",
                "password": "{{$randomPassword}}"
            },
            "images": [
                "{{$randomAbstractImage}}",
                "{{$randomAbstractImage}}",
                "{{$randomAbstractImage}}"
            ],
            "usage_video": "https://www.youtube.com/watch?v=jfKfPfyJRdk",
            "user_manual_url": "{{$randomUrl}}",
            "user_manual_file": "{{$randomUrl}}"
        }
    ],
    "status": "active",
    "created": "{{$isoTimestamp}}"
}

现有查询问题 #

当前使用的查询语句：

FOR p IN properties 
  FILTER p._id == @property_id
    FOR ss in outbound p provides
return merge (p, ss)

存在两个问题：

• 当属性关联多个服务时，会重复输出属性数据（每个服务对应一条属性记录）
• 当属性没有关联服务时，查询不会返回任何属性数据

解决方案 #

使用LET子句收集关联的services和appliances数组，再将其合并到属性文档中，确保无论是否有关联数据都能返回属性，且子文档以数组形式存在：

FOR p IN properties
  FILTER p._id == @property_id
  LET services = (FOR s IN outbound p provides RETURN s)
  LET appliances = (FOR a IN outbound p contains RETURN a)
  RETURN merge(p, { services: services, appliances: appliances })

说明 #

1. LET services = (...)：遍历provides边的 outbound 方向，收集当前属性关联的所有服务，返回数组（无服务时为空数组）
2. LET appliances = (...)：同理收集关联的所有家电
3. merge(p, { services: services, appliances: appliances })：将属性文档与包含子文档数组的对象合并，输出符合预期格式的结果

修改后效果：

• 无关联服务/家电时，属性数据正常返回，对应数组为空
• 多个关联子文档被整理成数组，不会重复输出属性数据

内容的提问来源于stack exchange，提问作者Enrico Di Marco