CosmosDB Query For Searching in a DB

Question

I have the entries in my CosmosDB that following this structure:

{
    "id": "~results:123",
    "catalog:entity": [
        {
            "id": "~song.base:1da",
            "schema:name": "Autobiography",
            "schema:code": [
                "7dhs"
            ]
        },
        {
            "id": "~song.base:2da",
            "schema:name": "Autobiography",
            "schema:code": [
                "4lko"
            ]
        }
    ]
}

However I'm having issues in getting searching for those that contain a specific schema:code: that in exists in an array with a parent array catalog:entity.

I've tried a handful of things such as with no luck:

SELECT * FROM c JOIN schema:code IN c["catalog:entity"] WHERE schema:code IN "7dhs"

generally I just keep seeing SELECT *' is only valid with a single input set.

Any ideas would be appreciated.

Thanks

I want to get any documents where the schema:code array contains a string 7dhs — userMod2
– userMod2, Commented Aug 1, 2019 at 2:18

Jay Gong · Accepted Answer · 2019-08-01 02:40:11Z

1

Use sql:

SELECT c FROM c 
JOIN s IN c["catalog:entity"]
where array_contains(s["schema:code"],"7dhs",false)

Output:

edited Aug 1, 2019 at 2:40

answered Aug 1, 2019 at 2:34

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

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

3 Comments

userMod2 Over a year ago

One thing - say I wanted to get teh entire document back SELECT * from c doenst seem to work

Jay Gong Over a year ago

@userMod2 Yes,same for me. You could try workaround: select c.a,c.b,c.c,c.d.... from c,maybe that could work.

userMod2 Over a year ago

Yep - that helps. Wonder why one can't use Select * in this case

Collectives™ on Stack Overflow

CosmosDB Query For Searching in a DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related