Query Across Partitions

When working with document databases, you can normally not query across two partitions.

For example, in the example below, if you want to find all people with postalCode 2500, you would have to make two calls. One for Northwind and one for Acme.

“person” type

partitionKey

id

userId

companyName

postalCode

contactEmail

Northwind

1

1

Northwind

2500

james@northwind.io

Northwind

2

2

Northwind

2500

joe@northwind.io

Acme

1

1

Acme

2500

jack@acme.lt

 

But views are the solution to everything, so how can you solve this with a view? Perhaps this could be the solution?

{
“id”: “LookupPeopleByPostalCode”,
“sourceRegion”: “germany",
“type”: “person”,
“partitionBy”: “postalCode”
}

This would result in disaster of lost data.

The reason being that with every row having the “postalCode” partition would result in the partition key being “2500” for all the shown rows. You would have an id collision. There are two rows here with id 1 and only one of them would survive in the view.

Instead, to keep the id unique, you would have to combine it with an idBy declaration in the view:

{
“id”: “LookupPeopleByPostalCode”,
“sourceRegion”: “germany",
“type”: “person”,
“partitionBy”: “postalCode”,
“idBy”: “companyName+userId”
}

Resulting in the following view:

person > LookupPeopleByPostalCode

partitionKey

id

userId

companyName

postalCode

contactEmail

2500

Northwind+1

1

Northwind

2500

james@northwind.io

2500

Northwind+2

2

Northwind

2500

joe@northwind.io

2500

Acme+1

1

Acme

2500

jack@acme.lt

 

It may look a little strange but keep focused on the goal.

You wanted to find all items with a certain postal code across all partitions. Mission accomplished.

The id is a technical detail that is used for identity and the artificial id created in this view will not be part of the objects that you retrieve when you call this view.