Most efficient way to run a recursive query in a container

Question

I’m relatively new to Azure Cosmos DB, and I am struggling with how to approach this problem due to some conflicting documentation.

I have a single container, with JSON data.

Each JSON document has a root level array called opcos which can contain N number of GUIDS (typically less than 5).

These opcos GUIDS refer to child items which are ID’s or separate documents.
If a parent document links to a child, then I need to check the child for more children in its opcos node.

Whats the best way to get all the related items, there could be approx. 100 related documents.

I need to keep each document separate, so I can’t store them as sub-documents, as link between parent and child is fluid between multiple parents.

I am looking for a recursive solution, and I am trying to do this from within Cosmos DB, as I am assuming that running potentially 100 calls from outside of Cosmos DB carries a performance overhead with all the connecting etc.

Advice is welcomed, I took a snippet off another article and tried editing it, but it immediately errors onvar context = getContext();

Also, any tips on debugging functions and stored procedures is welcome. I've 15 years of TSQL behind me, but this is very different.

When I tried using a function in Cosmos DB it says ReferenceError:

'getContext' is not defined

If I try the following code

  var context = getContext();
    var collection = context.getCollection();

    function userDefinedFunction(id){

        var context = getContext();
        var collection = context.getCollection();
        var metadataQuery = 'SELECT company.opcos FROM company where company.id in (' + id + ')';
        var metadata = collection.queryDocuments(collection.getSelfLink(), metadataQuery, {}, function (err, documents, options) {
            if (err) throw new Error('Error: ', + err.message);

            if (!documents || !documents.length) {
                throw new Error('Unable to find any documents');
            } else {
                var response = getContext().getResponse();

                /*for (var i = 0; i < documents.length; i++) {
                    var children = documents[i]['$1'].Children;

                    if (children.length) {
                        for (var j = 0; j < children.length; j++) {
                            var child = children[j];

                            children[j] = GetWikiChildren(child);
                        }
                    }
                }*/

                response.setBody(documents);
            }

        });

    }

Nick Chapsas Nick Chapsas · Accepted Answer · 2019-07-25T09:26:52

The answer really comes down to your partitioning strategy.

First and foremost your udf doesn't run because UDFs don't have the execution context as part of their API. Your function will run but you need to create it as a stored procedure, not a user defined function.

Now you have to keep in mind that stored procedures can be executed only against a single logical partition and this is their transaction scope. Your technique will work as long as you pass an array of ids in the stored procedure and the documents you're manipulating are in the same partition. If they are not then it's impossible to used a stored proc (well except if you have one per document which probably isn't worth it at this point).

On a side note you want to parameterize the way you add the ids in the query to prevent potential sql injection.

Most efficient way to run a recursive query in a container

1 Answers