How to search JSON in depth in Azure Cosmos DB?

Question

I have JSON files like below stored in Cosmos DB. I want to search text in activities/message.

{
  "id": "575858a7-f814-41fd-ae5a-6f38ba2da957",
  "name": "Test Name",
  "activities": [
    {
      "message": "activity 1.1 message",
      "messageType": "type1"
    },
    {
      "message": "activity 1.2 message",
      "messageType": "type2"
    }
  ]
}

I find out that I can use search like below.

SELECT * FROM c
WHERE CONTAINS(c.activities[0].message, "activity")

But, this can only search the first record in the array of activities. How to search all records in the array? Thanks.

Answer 1

You need to apply a join on your query, so then you array will be treated as a normalized entity and then you can apply the filter.

select c.id, a.message
from c join a in c.activities
where CONTAINS(a.message,"activity")

More info