Array without square brackets

Gregory Rose 6 Reputation points

I have a cosmo db container structured something like this:



[ {RaceID

Some of my data has square brackets [] around the RaceEntry field and some do not i.e. where there is only once instance of a RaceEntry. My problem is the documents that don't have the square brackets fail to be picked up when a run a join query - e.g. "SELECT , t.RaceDate FROM c JOIN t IN c.RaceEntry ".

Is there a way to restructure the query to cater for the 'bad' records, or alternatively how could I find all records where the square brackets are missing and correct them?

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,052 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Anurag Sharma 17,381 Reputation points

    Thanks for replying back and providing more details on the thread.

    So it seems whenever we have multiple elements in RaceEntry field then sqaure brackets are present and whenever we just have 1 element then brackets are missing. As rightly mentioned by you, this is incorrect schema and queries might not work correctly on these documents

    To find these bad documents we can write a simple query as mentioned below which will return all the records that have 1 element and missing square brackets:

    SELECT * from c where ARRAY_LENGTH([c.RaceEntry.RaceID]) = 1  

    Can you please run the query on your side once and let me know if you still face any issues?

    To correct these elements we need to update the documents either manually or using any SDKs.

    If going with SDKs, we can use the partial document update feature in Azure Cosmos.

    Partial document update in Azure Cosmos DB

    We can discuss more on this based on your feedback.

    1 person found this answer helpful.