Document modelling for nested objects

I have a use case where I have to deal with products information and it’s store related information together. Because, I have to fetch product and store information every time together, I decided to embed the information into a single document. But now, I’m confused what would be the best way to keep combined information.

For example,
I have a product document { “name” : “Apple”, “id”: 257481168 }
and its respective store documents as
{ “store”: 1000, “price”: 1.2}
{ “store”: 1001, “price”: 1.3}
{ “store”: 1002, “price”: 1.4}

Now, I have two ways of combining the documents into a single document.

Approach 1: Keep the store documents in an array in the combined document.

{ “name” : “Apple”, “id”: 257481168 , “stores”: [ { “store”: 1000, “price”: 1.2} , { “store”: 1001, “price”: 1.3} , { “store”: 1002, “price”: 1.4} ] }

Use UNNEST operation to retrieve product and specific store information

Approach 2: Maintain each store document as a different key in the main document.

{ “name” : “Apple”, “id”: 257481168 , “store:1000”: { “price”: 1.2} , “store:1001”: { “price”: 1.3} , “store:1002”: { “price”: 1.4} ] }

Use simple WHERE clause to retrieve product and specific store information

What would be the ideal way to form the combined document, approach 1 or 2. How does the N1ql query performance vary based on this ?

Hi Mahesh,

Option 1 is cleaner. It is a much better way to organize similar set of data in an array. UNNEST would allow you flatten the array object, but it does involve a self join. In any case, the right index would resolve any performance issue. Use index advisor if you are unsure what you need to create.

However you don’t always need to UNNEST, if your query need can be addressed by these ARRAY functions.

In addition, you can also create predicates directly on the nested array with these collection operators.

-binh

2 Likes

Thanks binh, this helps me. I was not aware of the array functions, I will definitely have a look.