Array without square brackets

Question

I have a cosmo db container structured something like this:

RIDER
{
id:

Fullname:

RaceEntry:
[ {RaceID
RaceDate
RaceResult}
]
}

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 c.id , 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?

Answer 1

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.