Azure CosmosDB MongoDB How To Query: Query Explorer

Question

I am exploring Azure CosmosDB and having trouble with understanding how I can query my collection. I created a web application to add and query the data but I want to search within Azure and do not know how I should structure my query.

Currently I query my collection running to return everything.

SELECT * FROM c

This returns a lot of information. Here is a sample of what the data looks like.

  {
    "$t": 3,
    "$v": {
      "searchTerm": {
        "$t": 2,
        "$v": "test"
      },
      "searchDate": {
        "$t": 9,
        "$v": 1501606675858
      },
      "_id": {
        "$t": 7,
        "$v": "Y³\u0013&Ò#\bø\u0005+ú"
      },
      "__v": {
        "$t": 16,
        "$v": 0
      }
    },
    "id": "NTk4MGIzMTMyNmQyMjMwOGY4MDUyYmZh",
    "_rid": "pR8YAPXxJQABAAAAAAAAAA==",
    "_self": "dbs/pR8YAA==/colls/pR8YAPXxJQA=/docs/pR8YAPXxJQABAAAAAAAAAA==/",
    "_etag": "\"020094b9-0000-0000-0000-5980b3130000\"",
    "_attachments": "attachments/",
    "_ts": 1501606672   }

Is there a way to just query for a certain "searchTerm" (a field within my CosmosDB). I cannot seem to grasp the Syntax to filter down my query. I have tried a combination of things that would seem to make sense in SQL but none of it is working.

Thanks for any help.

There is a an entire set of web documentation specifically around the DocumentDB SQL query language. I suggest you start there. Unfortunately, your question, as written, is essentially a request for such tutorial / learning content (or it's a broad-stroke question on querying in general against DocumentDB). A quick web search of documentdb sql or documentdb queries should give you everything you need to get started. — David Makogon
@DavidMakogon I am seeing basic examples of queries so maybe I should add that the $v is what is confusing me. In most examples there is not the additional meta data that it seems like Azure added. Do I include the $v in my query? For example c.$v.searchTerm? This seems to also throw an error. Thanks for any help! If there is any documentation for this I would be more than happy to read up on it. It just seems that every example I see does not discuss what the $v, $t actually means or why it is added. Sorry if this is a beginner mistake just trying to understand what I am doing wrong. — dev53
@tydev53 Did you create your collection and insert documents through the Mongo API? — Jesse Carter
I created my collection using express and mongoose. Maybe I need to define my schema better within my express app to get something that is easier to query. @JesseCarter — dev53

dev53 dev53 · Accepted Answer · 2017-08-02T16:45:51

With help from Jesse I was able to eventually get my query working using the SQL "Query Explorer" API.

As he explained since I am using an instance of MongoDb I should be using the Mongo Query API which can be accessed in Azure by going into "Data Explorer" -> select your collection -> "New Mongo Query". From there you can search using something similar to this...

{searchTerm: "test"}

Which returns all the searchTerms with the value test.

How To Do It With SQL Query Explorer

I wanted to do the same query with the SQL Query Explorer and after some trial and error the following syntax seems to work.

select *
from collection
where collection["$v"].searchTerm["$v"] = "test"

Not the prettiest solution but it worked. Just wanted to mention this in case someone else was trying to query their mongodb with the SQL Query Explorer.

I was not familiar with this syntax and all documentation I could find did not bring up anything about this unique case.

Thanks.

Azure CosmosDB MongoDB How To Query: Query Explorer

2 Answers