Cosmos db sql query for search in array

Question

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?

Jay Gong · Accepted Answer · 2020-01-07T08:07:22.110

2

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.

edited Jan 07 '20 at 08:07

answered Dec 19 '19 at 07:21

Jay Gong

23,163
2
27
32

1

looks cool, however I changed a little bit array because instead of [part.1, part2...] items i made another structure like [ {part: part.1}, {part: part.2} ... ] and managed to do query using user defined function similar to described here https://stackoverflow.com/questions/35137136/documentdb-sql-with-array-contains?noredirect=1&lq=1 and it works fine, but maybe I'll return to your solution. Depends on needs. Thanks! – Vrangz Jan 07 '20 at 08:03
@Vrangz It's ok. Sounds you are using udf in your sql.Anyway,glad to see you already passed through. If you don't mind, i summarized your solution in my answer so that you could accept the answer to end this case.Thank you. – Jay Gong Jan 07 '20 at 08:07

Cosmos db sql query for search in array

1 Answers1