AQL query for joining collections if not null and merging in single output

Question

I have a graph with the following structure

---
title: TEST GRAPH
---
erDiagram
    PROPERTIES ||--|{ APPLIANCES : contains 
    PROPERTIES ||--|{ SERVICES : provides

I would like to output in a single query a property with all his services and appliances represented like arrays

the final json should be something like

{
    "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}}"
}

thanks

so far I can build the output with

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

the problem with this query is that it duplicates the output in case of more services and if there are no services it does not return any property

any help is greatly appreciated

Answer 1

I managed to do it, posting here to help others in case. the working query is:

FOR p IN properties 
  FILTER p._id == @property_id 
RETURN MERGE(p, 
    {services: (FOR e in OUTBOUND p provides return distinct e)},
    {appliances:(FOR e in OUTBOUND p contains return distinct e)}
)