Querying documents in Azure Cosmos DB with Cosmos DB SQL Query Language often involves working with array fields—whether arrays of strings, numbers, or complex objects. A common scenario is:
“How do I find all documents where a particular item exists inside an array?”
Azure Cosmos DB provides powerful query capabilities, including the ARRAY_CONTAINS function, which allows you to search arrays efficiently. Below, you’ll learn how it works, see practical examples, and understand how to query documents containing specific list items.
1. Understanding ARRAY_CONTAINS
The ARRAY_CONTAINS() function checks whether an array contains a specific value or object. It returns a boolean value.
Syntax:
ARRAY_CONTAINS(<array_expr>, <expr> [, <bool_expr>])
- array_expr – The array field you want to search.
- expr – The value or object to find.
- bool_expr (optional) – Whether to allow partial object matching. Default is false.
Source: Microsoft Learn – ARRAY_CONTAINS documentation
2. Examples: Querying Arrays in Cosmos DB
2.1. Searching for a String in an Array
Imagine a document with a tags array:
[
{
"id": "item123",
"name": "Laptop Pro 15",
"tags": ["premium", "fashion", "sale"]
},
{
"id": "item124",
"name": "Wireless Mouse",
"tags": ["electronics", "accessories"]
},
{
"id": "item125",
"name": "Standing Desk",
"tags": ["office", "furniture"]
},
{
"id": "item126",
"name": "Noise Cancelling Headphones",
"tags": ["audio", "electronics", "premium"]
},
{
"id": "item127",
"name": "LED Desk Lamp",
"tags": ["lighting", "office", "sale"]
}
]
To find all documents that contain “premium”:
SELECT *
FROM c
WHERE ARRAY_CONTAINS(c.tags, "premium")
This matches any document where “premium” is a tag.
2.2 Finding an Item in an Array by a Specific id
This is one of the most common array‑lookup scenarios: finding an object inside an array by its id property.
Example document:
[
{
"id": "order1001",
"customer": { "id": "cust001", "name": "John Doe" },
"items": [
{ "id": "item1", "name": "Laptop", "qty": 1 },
{ "id": "item2", "name": "Mouse", "qty": 2 }
],
"tags": ["priority", "fragile"]
},
{
"id": "order1002",
"customer": { "id": "cust002", "name": "Anna Smith" },
"items": [
{ "id": "item3", "name": "Keyboard", "qty": 1 },
{ "id": "item4", "name": "Desk Lamp", "qty": 2 }
],
"tags": ["standard"]
},
{
"id": "order1003",
"customer": { "id": "cust003", "name": "Michael Brown" },
"items": [
{ "id": "item5", "name": "Monitor", "qty": 1 },
{ "id": "item6", "name": "USB Hub", "qty": 1 }
],
"tags": ["warehouse-3", "priority"]
},
{
"id": "order1004",
"customer": { "id": "cust004", "name": "Sarah Lee" },
"items": [
{ "id": "item7", "name": "Chair", "qty": 1 },
{ "id": "item8", "name": "Desk", "qty": 1 }
],
"tags": ["bulk"]
}
]
Partial object match by id:
SELECT *
FROM c
WHERE ARRAY_CONTAINS(
c.items,
{ "id": "item2" },
true
)
This will return any document where at least one item in the array contains the matching “id”. This is supported through the bool_expr = true option
3. Summary
ARRAY_CONTAINS is a powerful and simple way to search arrays in Cosmos DB. It is ideal when:
- You need to check whether a string exists in an array
- You want to match a full object
- You want to match part of an object using partial matching
- You want to find an array item by a specific id without needing JOIN or EXISTS
It enables clean, readable, and efficient queries for typical array scenarios in Cosmos DB.