Cosmos db sql query for search in array

I have a document structure like below

[
    {
        "id": "1",
        "author": "John Smith",
        "partNames": [
            "PART.5",
            "PART.10",
            "PART.15",
            "PART.30",
            "PART.31"
        ]
        ...
    },
    {
        "id": "2",
        "author": "Mike Smith",
        "partNames": [
            "PART.4",
            "PART.6",
            "PART.17",
            "PART.18",
            "PART.20"
        ]
        ...
    }
]

and I try to implement searching for user by fields author and partNames, so user can do query like

SELECT * FROM c WHERE CONTAINS(c.author, 'John')

and user will get in result the id=1 document because the name of author was John.

The next step is to allow user ask for document by parts, so for example I want to receive a document where PART.4 exists, so the query will be

SELECT * FROM c WHERE ARRAY_CONTAINS(c.partNames, 'PART.4', true)

and it returns document id=2.

Going further I’d like to make query that allows user to type just PART.1 and he will receive documents id=1 and id=2 because both have parts starting with PART.1

So is there a way to achieve it?

Answer

Please test below sql:

SELECT distinct value c FROM c
join p in c.partNames
where STARTSWITH(p, "PART.1")

Results:

---

Updates:

Op changed a little bit array because instead of [part.1, part2...] items and made another structure like [ {part: part.1}, {part: part.2} ... ] and managed to do query using user defined function similar to described here DocumentDB SQL with ARRAY_CONTAINS and it works fine.