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-07 08:07:22Z

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 7, 2020 at 8:07

answered Dec 19, 2019 at 7:21

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vrangz Over a year ago

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 stackoverflow.com/questions/35137136/… and it works fine, but maybe I'll return to your solution. Depends on needs. Thanks!

Jay Gong Over a year ago

@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.

Collectives™ on Stack Overflow

Cosmos db sql query for search in array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related