Array without square brackets

Question

question

GregoryRose-3995 asked Apr 20, '22 AnuragSharma-MSFT commented Apr 23, '22

Array without square brackets

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?

azure-cosmos-db

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT · Apr 20 at 02:56 PM

Hi @GregoryRose-3995, welcome to Microsoft Q&A forum.

Would it be possible to share examples of each of bad and good record? That way we can try to reproduce it on exact data and will try to find the solution.

0 ·

GregoryRose-3995 AnuragSharma-MSFT · Apr 21 at 03:24 AM

Thanks for you reply - here are 2 examples ; id "1" is the correct format with square brackets, id "2" is the problem example:

{
"id": "1",
"Surname": "ABC",
"FirstName": "Christopher",
"RaceEntry": [
{
"RaceID": 734,
"RaceDate": "2015-11-15",
"RaceVenue": "Steel River",
"RaceResult": "3rd"
},
{
"RaceID": 740,
"RaceDate": "2015-12-06",
"RaceType": "Crit",
"RaceVenue": "Steel River"
}]
}

{
"id": "2",
"Surname": "xyz",
"FirstName": "PAMELA",
"RaceEntry":
{
"RaceID": 734,
"RaceDate": "2015-11-15",
"RaceVenue": "Steel River",
"RaceResult": "2nd"
}
}

0 ·

HasanSavran-7728 · Apr 20 at 06:33 PM

Have you tried to use a subquery in JOIN?

0 ·

Answer 1 · 2022-04-21T06:43:39.683Z

AnuragSharma-MSFT answered Apr 21, '22 AnuragSharma-MSFT commented Apr 23, '22

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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GregoryRose-3995 · Apr 21 at 08:07 AM

Thanks so much - the query yielded 139 records, which is about what I expected.

I'd be interested in trying the partial update if you could offer some guidance.

Regards

Greg

0 ·

AnuragSharma-MSFT GregoryRose-3995 · Apr 22 at 07:17 AM

Thanks for confirming the query is retrieving the results.

For using partial document update feature, we can refer to below link which has end to end code written in .NET.

partial document update sample can be found in the .NET v3 samples repository

However if you are not comfortable with .NET and the number of documents are very less we can do it through manual changes and then updating the document back to azure account using Azure Cosmos DB Data Migration Tool as well:

We can create a json file and add multiple documents in it (I have attached the sample file with multiple documents in this comment), and then checking below properties as shown in screenshot:

195463-jsonupdatecosmos.txt

Tutorial: Use Data migration tool to migrate your data to Azure Cosmos DB

Please let us know if this helps.

1 ·

image.png (74.7 KiB)

jsonupdatecosmos.txt (975 B)

GregoryRose-3995 AnuragSharma-MSFT · Apr 23 at 10:12 AM

Thanks so much for your wonderful advice. I will give both those suggestions a try - I can see many use cases that will be supported by both of these options.

Regards

Greg

1 ·

Show more comments

question