How can I select a filtered child document collection when querying a top level document in cosmos db

Question

I'm trying to filter the child documents returned when querying a parent document using the sql api in cosmos db.

For example given this document:

{
    "customerName": "Wallace",
    "customerReference": 666777,
    "orders": [
    {
        "date": "20181105T00:00:00",
        "amount": 118.84,
        "description": "Laptop Battery"
    },
    {
        "date": "20181105T00:00:00",
        "amount": 81.27,
        "description": "Toner"
    },
    {
        "date": "20181105T00:00:00",
        "amount": 55.12,
        "description": "Business Cards"
    },
    {
        "date": "20181105T00:00:00",
        "amount": 281.00,
        "description": "Espresso Machine"
    }]    
    }

I would like to query the customer to retrieve the name, reference and orders over 100.00 to produce a results like this

[{
"customerName": "Wallace",
"customerReference": 666777,
"orders": [
    {
        "date": "20181105T00:00:00",
        "amount": 118.84,
        "description": "Laptop Battery"
    },           
    {
        "date": "20181105T00:00:00",
        "amount": 281.00,
        "description": "Espresso Machine"
    }]
}]

the query I have so far is as follows

SELECT c.customerName, c.customerReference, c.orders
from c
where c.customerReference = 666777
and c.orders.amount > 100

this returns an empty set

[]

and if you remove "and c.orders.amount > 100" it matches the document and returns all orders.

To reproduce this issue I simply set up a new database, added a new collection and copied the json example in as the only document. The index policy is left as the default which I've copied below.

{
"indexingMode": "consistent",
"automatic": true,
"includedPaths": [
    {
        "path": "/*",
        "indexes": [
            {
                "kind": "Range",
                "dataType": "Number",
                "precision": -1
            },
            {
                "kind": "Range",
                "dataType": "String",
                "precision": -1
            },
            {
                "kind": "Spatial",
                "dataType": "Point"
            }
        ]
    }
],
"excludedPaths": []
}

Yeap, if something isn't indexed you cannot for filtering against it, and even if it is indexed, it has to be at least a Range type of index. let me know if that worked and i will write up the answer — Nick Chapsas
added an range index for Number on /orders/* with no effect, still returns [] — Twisted
Did you give it some time to re-index your properties. Also do you have automatic indexing turned on? Also whats the indexing type on numbers? — Nick Chapsas

Twisted Twisted · Accepted Answer · 2018-11-26T10:44:32

Cosmos DB doesn't support the deep filtering in the way I attempted in my original query.

To achieve the results described you need to use a subquery using a combination of ARRAY and VALUE as follows:

SELECT 
    c.customerName, 
    c.customerReference, 
    ARRAY(SELECT Value ord from ord in c.orders WHERE ord.amount > 100) orders
from c
    where c.customerReference = 666777

note the use of 'ord' - 'order' is a reserved word.

The query then produces the correct result - eg

[{
    "customerName": "Wallace",
    "customerReference": 666777,
    "orders": [
        {
            "date": "20181105T00:00:00",
            "amount": 118.84,
            "description": "Laptop Battery"
        },           
        {
            "date": "20181105T00:00:00",
            "amount": 281.00,
            "description": "Espresso Machine"
        }
     ]
}]

How can I select a filtered child document collection when querying a top level document in cosmos db

1 Answers