Cosmos sql query which doesn't return an array

Question

I'm trying to query a Cosmos Database to find out the ids of all of its containers.

Using the below code it gives me a json response that is a single item (not an array).

Is there a way to write the query so that I only get an array of the 'id' fields from the DocumentCollection. e.g. something like

 [ { "id": "Summary-v00019"}, {"id": "Details-V00019"} ]

using Microsoft.Azure.Cosmos;
using System;
using System.IO;

namespace QueryDatabase
{
    class Program
    {
        const string CosmosConnectionString = "AccountEndpoint=https://some-cosmos-ccount.documents.azure.com:443/;AccountKey=blahblah==;";

        const string Database = "database-name";

        static async System.Threading.Tasks.Task Main()
        {

            var cosmosClient = new CosmosClient(CosmosConnectionString);

            var database = cosmosClient.GetDatabase(Database);

            string queryText = "SELECT * FROM c";
            QueryDefinition queryDefinition = new QueryDefinition(queryText);
            FeedIterator feedIterator = database.GetContainerQueryStreamIterator(queryDefinition);
            while (feedIterator.HasMoreResults)
            {
                using (ResponseMessage response = await feedIterator.ReadNextAsync())
                {
                    using (var sr = new StreamReader(response.Content))
                    {
                        var text = sr.ReadToEnd();
                        Console.WriteLine(text);
                    }
                }
            }
        }
    }
}

JSON Response from above code:

{
    "_rid": "blah==",
    "DocumentCollections": [
        {
            "id": "Summary-v00019",
            "indexingPolicy": {
                "indexingMode": "consistent",
                "automatic": true,
                "includedPaths": [
                    {
                        "path": "/*"
                    }
                ],
                "excludedPaths": [
                    {
                        "path": "/\"_etag\"/?"
                    }
                ]
            },
            "partitionKey": {
                "paths": [
                    "/foo"
                ],
                "kind": "Hash"
            },
            "conflictResolutionPolicy": {
                "mode": "LastWriterWins",
                "conflictResolutionPath": "/_ts",
                "conflictResolutionProcedure": ""
            },
            "geospatialConfig": {
                "type": "Geography"
            },
            "_rid": "blah=",
            "_ts": 1591935021,
            "_self": "dbs/blah==/colls/blah=/",
            "_etag": "\"blah-blah-blah-0000-blah\"",
            "_docs": "docs/",
            "_sprocs": "sprocs/",
            "_triggers": "triggers/",
            "_udfs": "udfs/",
            "_conflicts": "conflicts/"
        },
        {
            "id": "Details-v00019",
            "indexingPolicy": {
                "indexingMode": "none",
                "automatic": false,
                "includedPaths": [],
                "excludedPaths": []
            },
            "partitionKey": {
                "paths": [
                    "/bar"
                ],
                "kind": "Hash"
            },
            "conflictResolutionPolicy": {
                "mode": "LastWriterWins",
                "conflictResolutionPath": "/_ts",
                "conflictResolutionProcedure": ""
            },
            "geospatialConfig": {
                "type": "Geography"
            },
            "_rid": "blah=",
            "_ts": 1591935021,
            "_self": "dbs/blah==/colls/blah=/",
            "_etag": "\"blah-0000-blah-0000-blah\"",
            "_docs": "docs/",
            "_sprocs": "sprocs/",
            "_triggers": "triggers/",
            "_udfs": "udfs/",
            "_conflicts": "conflicts/"
        }
    ],
    "_count": 2
}

Try something like "SELECT c.DocumentCollections.id FROM c". — Gaurav Mantri
– Gaurav Mantri, Commented Jun 16, 2020 at 5:34
@GauravMantri-AIS I had tried a few different variations. Your suggestion returns: {"_rid":"blah==","DocumentCollections":[{},{}],"_count":2} — camios
– camios, Commented Jun 16, 2020 at 5:54

J82W · Accepted Answer · 2020-06-18 11:48:12Z

Cosmos DB returns all all query, read feed, and change feed operations in a wrapper.

Here are some examples of how to do it using the 3.10.0 release where FeedIterator now implements IDisposable to fix a memory leak.

  using (FeedIterator feedIterator = this.cosmosDatabase.GetContainerQueryStreamIterator(
                "select value c.id From c "))
{
   // The current logic is not handling if there is a loop.
    while (feedIterator.HasMoreResults)
    {
        using (ResponseMessage response = await feedIterator.ReadNextAsync())
        {
            response.EnsureSuccessStatusCode();
            using (StreamReader streamReader = new StreamReader(response.Content))
            {
                string content = await streamReader.ReadToEndAsync();
                // Output will be:
                // {"_rid":"7p8wAA==","DocumentCollections":["4d310b0d-1716-4bc8-adfa-861a66e4034b","e3eb4ac7-f8a4-47ce-bd71-f65ab43dcb53"],"_count":2}
                int arrayStart = content.IndexOf('[');
                int arrayEnd = content.IndexOf(']');
                string json = content.Substring(arrayStart, arrayEnd - arrayStart + 1);
            }
        }
    }
}

This is an example using the typed API. You shouldn't be querying the list of containers that frequently so using the typed API might be worth the performance hit.

List<string> ids = new List<string>();
using (FeedIterator<string> feedIterator = this.cosmosDatabase.GetContainerQueryIterator<string>(
        "select value c.id From c "))
{
    while (feedIterator.HasMoreResults)
    {
        FeedResponse<string> iterator = await feedIterator.ReadNextAsync();
        ids.AddRange(iterator);
    }
}

string json = JsonConvert.SerializeObject(ids);

Imre Pühvel · Accepted Answer · 2020-06-17 07:25:20Z

1

First of all, there really is something fishy about select statement NOT selecting what you instruct it to do. Most likely this is a cosmosDB bug.

What seems to happen, is that your select results are always returned in a fixed wrapper of:

{
    "_rid": "...",
    "DocumentCollections": [  ], // .. your select applies here .. 
    "_count": N
}

There seems to be no way to get rid of this wrapper. What you can do though, is limit what's inside DocumentCollections. For example, use query:

SELECT c.id FROM c

Which should produce the output of:

{
    "_rid":"blah==",
    "DocumentCollections":[ 
        {"id": "Summary-v00019"}, 
        {"id": "Details-V00019"} 
    ],
    "_count":2
}

I think this is as close as you are going to get until MS fixes it. And they may not even fix this due to being a low-priority breaking change.

answered Jun 17, 2020 at 7:25

Imre Pühvel

5,1341 gold badge42 silver badges55 bronze badges

2 Comments

camios Over a year ago

Unfortunately I had already tried “SELECT c.id FROM c” and it throws an exception

Imre Pühvel Over a year ago

I think you tried "SELECT id FROM c", not "SELECT c.id FROM c". Worked when I tested it.

Steve Johnson · Accepted Answer · 2020-06-16 07:34:43Z

0

Try this:

static async System.Threading.Tasks.Task Main()
    {

        var cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);

        var database = cosmosClient.GetDatabase(databaseId);
        string queryText = "SELECT c.id FROM c";
        QueryDefinition queryDefinition = new QueryDefinition(queryText);
        FeedIterator feedIterator = database.GetContainerQueryStreamIterator(queryDefinition);
        while (feedIterator.HasMoreResults)
        {
            using (ResponseMessage response = await feedIterator.ReadNextAsync())
            {
                using (var sr = new StreamReader(response.Content))
                {
                    string text = sr.ReadToEnd();
                    Console.WriteLine(text);
                    JObject json = (JObject)JsonConvert.DeserializeObject(text);
                    Console.WriteLine(json["DocumentCollections"]);
                }
            }
        }
    }

Result:

{"_rid":"R-5RAA==","DocumentCollections":[{"id":"orders"},{"id":"user"},{"id":"employee"}],"_count":3}
[
  {
    "id": "orders"
  },
  {
    "id": "user"
  },
  {
    "id": "employee"
  }
]

Hope this can help you.

answered Jun 16, 2020 at 7:34

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

2 Comments

camios Over a year ago

I am able to extract the ids out of the larger JSON, I am interested in a queryText that just returns list of ids, rather than extracting it out of the JSON, thanks

Steve Johnson Over a year ago

As Imre Pühvel says,it is impossible to do this.You need to extract it out of the JSON.

Collectives™ on Stack Overflow

Cosmos sql query which doesn't return an array

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related