How to join tree (?) correctly?

#1

Hi,

I’m trying to join a tree like structure.

I have designed my documents as below.

productType1

"attributes": [
    "attribute1",
    "attribute2"
]

attribute1

"values": [
    "value1",
    "value2"
]

I want to query productTypes then include attributes in productTypes then include values in attributes. All of the documents are in the same bucket.

I’ve tried this query:

select * from mybucket productType use keys[‘productType1’]
join mybucket attributes
on keys productType.attributes
join mybucket values
on keys attributes.values;

The query gives somewhat OKAY response, but with many duplicates and incorrect format.

Is there a way to get the response I want? if not, is there a way to simply query all of the productTypes, attributes, and values so that I can sort them in code?

#2

In your query, use NEST instead of JOIN.

#3

Hi @geraldss

I’ve tried nest, but It did not work how I expected. Here is what I did exactly.

Documents

productType

{
  "name": "aProductType",
  "associated_attributes": [
    "attribute1"
  ]
}

attribute1

{
    "name": "aProductType",
    "associated_attribute_values": [
        "attributeValue1"
    ]
}

attributeValue1

{
  "name": "anAttributeValue"
}

Nesting productType and attribute returns the following (select * from test productType use keys “productType” nest test attribute on keys productType.associated_attributes ).

{
    "attribute": [
        {
            "associated_attribute_values": [
                "attributeValue1"
            ],
            "name": "aProductType"
        }
    ],
    "productType": {
        "associated_attributes": [
            "attribute1"
        ],
        "name": "aProductType"
    }
}

The following query returns an empty result.

select * from test productType use keys "productType" nest test attribute on keys productType.associated_attributes nest test attributeValues on keys attribute.associated_attribute_values;

My desired output:

{
    "attribute": [
        {
            "associated_attribute_values": [
                "attributeValue1"
            ],
            "name": "aProductType"
        }
    ],
    "productType": {
        "associated_attributes": [
            "attribute1"
        ],
        "name": "aProductType",
        "attributes": [
            {
                "associated_attribute_values": [
                    "attributeValue1"
                ],
                "name": "aProductType",
                "attribute_values": [
                    {
                        "name": "anAttributeValue"
                    }
                ]
            }
        ]
    }
}

Is this possible with n1ql? I want to query all the attributes and attribute values with one query. Otherwise, I have to loop and query one by one, which isn’t really desired.

#4

Ok, please try the following. Note that USE KEYS requires the actual external keys (id’s) of the documents you want to query.

SELECT *,
    (SELECT *,
         (SELECT * FROM test USE KEYS attr.associated_attribute_values) AS attribute_values
     FROM test AS attr USE KEYS product.associated_attributes) AS attributes
FROM test AS product USE KEYS "product1"
#5

Thank you! That’s exactly what I wanted!