Cosmos DB: getContext().getCollection().queryDocuments does not return items in SP

Question

I just started with Cosmos DB and I am left puzzled why I won't get any documents returned using a simple SP query.

This is what I have done:

• created Cosmos DB database with partition key "/id"
• created catalogue "Families"
• created two items:

    {
        "id": "AndersenFamily",
        "lastName": "Andersen",
        "parents": [
            {
                "firstName": "Thomas"
            },
            {
                "firstName": "Mary Kay"
            }
        ],
        "children": [
            {
                "firstName": "Henriette Thaulow",
                "gender": "female",
                "grade": 5,
                "pets": [
                    {
                        "givenName": "Fluffy"
                    }
                ]
            }
        ],
        "address": {
            "state": "WA",
            "county": "King",
            "city": "Seattle"
        },
        "creationDate": 1431620472,
        "isRegistered": true
    },
    {
        "id": "WakefieldFamily",
        "parents": [
            {
                "familyName": "Wakefield",
                "givenName": "Robin"
            },
            {
                "familyName": "Miller",
                "givenName": "Ben"
            }
        ],
        "children": [
            {
                "familyName": "Merriam",
                "givenName": "Jesse",
                "gender": "female",
                "grade": 1,
                "pets": [
                    {
                        "givenName": "Goofy"
                    },
                    {
                        "givenName": "Shadow"
                    }
                ]
            },
            {
                "familyName": "Miller",
                "givenName": "Lisa",
                "gender": "female",
                "grade": 8
            }
        ],
        "address": {
            "state": "NY",
            "county": "Manhattan",
            "city": "NY"
        },
        "creationDate": 1431620462,
        "isRegistered": false
    }

• running query "SELECT * FROM c" returns both records as expected
• However, the queryDocuments method in below SP always retrieves zero length for feed. Why aren't both items returned?

    // SAMPLE STORED PROCEDURE
    function sample(prefix) {
        // Query documents and take 1st item.
        var isAccepted = getContext().getCollection().queryDocuments(
            getContext().getCollection().getSelfLink(),
            'SELECT * FROM root r',
        function (err, feed, options) {
            if (err) throw err;
    
            console.log("Feed length: " + feed.length);
    
            // Check the feed and if empty, set the body to 'no docs found', 
            // else take 1st element from feed
            if (!feed || !feed.length) {
                var response = getContext().getResponse();
                response.setBody('no docs found');
            }
            else {
                var response = getContext().getResponse();
                var body = { prefix: prefix, feed: feed[0] };
                response.setBody(JSON.stringify(body));
            }
        });
    
        if (!isAccepted) throw new Error('The query was not accepted by the server.');
    }

Many thanks for your help!

Answer 1

SPs in Cosmos db work based on partition key, that means all the documents within a partition will be returned and not cross partition ones. You can execute the SP as mentioned in below screenshot. As per the official doc "For partitioned containers, when executing a stored procedure, a partition key value must be provided in the request options. Stored procedures are always scoped to a partition key. Items that have a different partition key value will not be visible to the stored procedure. This also applied to triggers as well."