Nested "for loop" searches in SQL - Azure CosmosDB

Question

I am using Cosmos DB and have a document with the following simplified structure:

{
    "id1":"123",
    "stuff": [
        {
            "id2": "stuff",
            "a": {
                "b": {
                    "c": {
                        "d": [
                            {
                                "e": [
                                    {
                                        "id3": "things",
                                        "name": "animals",
                                        "classes": [
                                            {
                                                "name": "ostrich",
                                                "meta": 1
                                            },
                                            {
                                                "name": "big ostrich",
                                                "meta": 1
                                            }
                                        ]
                                    },
                                    {
                                        "id3": "default",
                                        "name": "other",
                                        "classes": [
                                            {
                                                "name": "green trees",
                                                "meta": 1
                                            },
                                            {
                                                "name": "trees",
                                                "score": 1
                                            }
                                        ]
                                    }
                                ]
                            }
                        ]
                    }
                }
            }
        }
    ]
}

My issue is - I have an array of these documents and need to search name to see if it matches my search word. For example I want both big trees and trees to return if a user types in trees.

So currently I push every document into an array and do the following:

For each document

for each stuff

for each a.b.c.d[0].e

for each classes var splice = name.split(' ')

if (splice.includes(searchWord))

return id1, id2 and id3.

Using cosmosDB I am using SQL with the following code:

client.queryDocuments(
    collection,
    `SELECT * FROM root r`
).toArray((err, results) => {stuff});

This effectively brings every document in my collection into an array to perform the search manually above as mentioned.

This is going to cause issues when I have 1000s or 1,000,000s of documents in the array and I believe I should be leveraging the search mechanics available within Cosmos itself. Is anyone able to help me to work out what SQL query would be able to perform this type of function?

Having searched everything is it also possible to search the 5 latest documents?

Thanks for any insight in advance!

Jay Gong Jay Gong · Accepted Answer · 2018-09-17T08:20:44

1.Is anyone able to help me to work out what SQL query would be able to perform this type of function?

According to your sample and description, I suggest you using ARRAY_CONTAINS in cosmos db sql. Please refer to my sample:

sample documents:

[
    {
        "id1": "123",
        "stuff": [
            {
                "id2": "stuff",
                "a": {
                    "b": {
                        "c": {
                            "d": [
                                {
                                    "e": [
                                        {
                                            "id3": "things",
                                            "name": "animals",
                                            "classes": [
                                                {
                                                    "name": "ostrich",
                                                    "meta": 1
                                                },
                                                {
                                                    "name": "big ostrich",
                                                    "meta": 1
                                                }
                                            ]
                                        },
                                        {
                                            "id3": "default",
                                            "name": "other",
                                            "classes": [
                                                {
                                                    "name": "green trees",
                                                    "meta": 1
                                                },
                                                {
                                                    "name": "trees",
                                                    "score": 1
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        ]
    },
    {
        "id1": "456",
        "stuff": [
            {
                "id2": "stuff2",
                "a": {
                    "b": {
                        "c": {
                            "d": [
                                {
                                    "e": [
                                        {
                                            "id3": "things2",
                                            "name": "animals",
                                            "classes": [
                                                {
                                                    "name": "ostrich",
                                                    "meta": 1
                                                },
                                                {
                                                    "name": "trees",
                                                    "meta": 1
                                                }
                                            ]
                                        },
                                        {
                                            "id3": "default2",
                                            "name": "other",
                                            "classes": [
                                                {
                                                    "name": "green trees",
                                                    "meta": 1
                                                },
                                                {
                                                    "name": "trees",
                                                    "score": 1
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        ]
    },
    {
        "id1": "789",
        "stuff": [
            {
                "id2": "stuff3",
                "a": {
                    "b": {
                        "c": {
                            "d": [
                                {
                                    "e": [
                                        {
                                            "id3": "things3",
                                            "name": "animals",
                                            "classes": [
                                                {
                                                    "name": "ostrich",
                                                    "meta": 1
                                                },
                                                {
                                                    "name": "big",
                                                    "meta": 1
                                                }
                                            ]
                                        },
                                        {
                                            "id3": "default3",
                                            "name": "other",
                                            "classes": [
                                                {
                                                    "name": "big trees",
                                                    "meta": 1
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        ]
    }
]

query :

SELECT distinct c.id1,stuff.id2,e.id3 FROM c
join stuff in c.stuff
join d in stuff.a.b.c.d
join e in  d.e
where ARRAY_CONTAINS(e.classes,{name:"trees"},true)
or ARRAY_CONTAINS(e.classes,{name:"big trees"},true)

output:

2.Having searched everything is it also possible to search the 5 latest documents?

Per my research, features like LIMIT is not supported in cosmos so far. However , TOP is supported by cosmos db. So if you could add sort field(such as date or id), then you could use sql:

select top 5 from c order by c.sort desc

Nested "for loop" searches in SQL - Azure CosmosDB

1 Answers