Cosmos DB query element count

Question

I would like to find find how many times an element occurs.From the sample I want to get the element count. If we see the data. Elements appear twice also the inner element like Name appears twice. Like that I want to find the count of element occurrence.

I tried seeing some group by clause and could not find any group by option in CosmosDB.

{ "data": [ [ { "Elements": [ { "Name": "Ma", "Gender": "Male", "City": "Newyork", "State": "Newyork" }, { "Gender": "Female", "City": "Los Angeles", "State": "California" }, { "Name": "Bruce", "Gender": "Male", "City": "Chicago" } ], "Subdivided": [ { "Low": "20", "Block": "227", "Subcode": "MIRROR", "Legal_Desc_Map_Plat_Type_Indicator": "PLA" } ], "Elements": [ { "Name": "Ma", "Gender": "Male", "City": "Newyork" }, { "Gender": "Female", "City": "Los Angeles", "State": "California" }, { "Name": "Bruce", "Gender": "Male", "State": "Illinois" } ] } ] ], "_ts": 1559463991 }

Result something like. Element = 2, Element.Name = 2 etc like that.

not sure what's your meaning. data[0] is based on your data format,if you remove the Outermost layer [], you don't need data[0]. — Jay Gong

Jay Gong Jay Gong · Accepted Answer · 2019-07-16T02:56:58

Firstly,your screenshot is impossible.Elements key can't duplicate in single object.

Your requirement could be implemented by the COUNT aggregate function.

For Elements:

SELECT value(count(data.Elements))
FROM c
join data in c.data

Result:

For Name:

SELECT value(count(elem.Name))
FROM c
join data in c.data
join elem in data.Elements

Result:

For your further need:

Since Aggregates are not allowed in the WHERE clause,

maybe you need to process the results by yourself. Group By is not supported naturally(feedback),however you could get know about a great package documentdb-lumenize based on DocumentDb stored procedure. You could count the elements and group by some other columns,like id.

Cosmos DB query element count

1 Answers