How to query Cosmos DB on Azure Portal with $v and $t

Question

I have a Cosmos DB on Azure with SQL API. I am able to query data through C# using their Nuget package, but I am getting an error when running a query from Azure Portal > Cosmos DB > Data Explorer.

I'd like to get the value of $v and do some filtering based on it.

Using

SELECT c.id, c.Remarks FROM c

I am getting results with nested objects (see the result below). But I need only one value from the nested object.

I tried changing the syntax to

SELECT c.id, c.Remarks.$v FROM c

or

SELECT c.id, c.Remarks.v FROM c

or

SELECT c.id, c.Remarks/$v FROM c

but I am getting an error.

Current Results:

[
    {
        "id": "e9f3ae8e47ab4bbca97dadf3ff1fe08c",
        "Remarks": {
            "$t": 2,
            "$v": "Success"
        }
    },
    {
        "id": "97bea2e9919c48f2bde83c11c50e8177",
        "Remarks": {
            "$t": 2,
            "$v": "Failure"
        }
    },
    {
        "id": "bb142e17b8184d5c84a21aa2e218e3be",
        "Remarks": {
            "$t": 2,
            "$v": "Success"
        }
    }
]

Expected Results:

I'd like to see (and preferably apply filter to only see only Failures)

[
    {
        "id": "e9f3ae8e47ab4bbca97dadf3ff1fe08c",
        "Remarks": "Success"
    },
    {
        "id": "97bea2e9919c48f2bde83c11c50e8177",
        "Remarks": "Failure"
    },
    {
        "id": "bb142e17b8184d5c84a21aa2e218e3be",
        "Remarks": "Success"
    }
]

I'm encountering errors like

Syntax error, invalid token '$'

but I cannot find any solution.

user_3662111 · Answer

It looks like you're trying to use the SQL API against a document that was inserted via the Mongo endpoint. That's what causes the $t/$v stuff.

Mixing and matching Mongo and SQL APIs is a little complex and not really recommended.

You can do one of a few things, in no particular order.

Use Mongo to do queries
Recreate your collection using just SQL APIs
Checkout this answer about how to write SQL API queries against Mongo: Cosmos DB + pyDocumentDB + Databricks (You've gotta do little [] to access things with a $ at the beginning of them, like so: SELECT TOP 10 twttr["$v"].tweet["$v"].source FROM twttr)

I personally recommend not doing #3 unless you have to. If you want to use SQL, I'd recommend just using SQL and going with #2.

How to query Cosmos DB on Azure Portal with $v and $t

1 Answers