Cosmos DB SQL Query how to count sub properties?

Question

I have these kind of json documents in a CosmosDB database.

{
    "Version": 0,
    "Entity": {
        "ID": "xxxxxxx",
        "EventHistory": {
            "2020-04-28T16:30:35.6887561Z": "NEW",
            "2020-04-28T16:35:21.1811993Z": "PROCESSED"
        },
        "SourceSystem": "xxxx",
        "SourceSystemIdentifier": "xxxx",
        "PCC": "xxx",
        "StorageReference": "xxxxxxxxxxxx",
        "SupplementaryData": {
            "eTicketCount": "2"
        }
    }
}

The number of sub-properties within the EventHistory node is dynamic. In the example there are two but it can be any number.

I couldn't find a way to count how many sub-properties the node contains. At least, I need to query those whose have only one property declared.

FYI: I'm not able to change the format of the documents. I know that it would be more convenient to store them as an array.

I tried to use ARRAY_LENGTH or COUNT functions but since it's not an array, the formers couldn't be applied.

FYI your data model is really showing an anti-pattern, using values as keys in EventHistory (I explain this anti-pattern in this answer). EventHistory should be an array of subdocuments. — David Makogon
– David Makogon, Commented Nov 5, 2022 at 15:30

jaybro · Accepted Answer · 2023-05-31 14:38:00Z

0

Perhaps you could figure out how many properties are in EventHistory by calculating its length as text. Something like this query could find you entries with just one property.

select * from c 
where (0 < LENGTH(ToString(c.Entity.EventHistory)) and
  LENGTH(ToString(c.Entity.EventHistory)) <= maxLengthOfOneProperty)

The drawback is: each progressive length range will get less accurate with more properties and/or values that greatly vary in length.

answered May 31, 2023 at 14:38

jaybro

1,5931 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cosmos DB SQL Query how to count sub properties?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related