Querying ConsmosDB Using SQL API

Introduction

  • CosmosDB supports an SQL grammar for querying the documents.
    • Familiar SQL syntax like SELECT, FROM, WHERE, JOIN, IN, BETWEEN, ORDER BY
    • Iterate nested arrays
    • Return documents “as-is” or shape JSON to fit your needs
  • Common operators
    • Arithmetic operators: +,-,*,/,%
    • Bitwise operators: |,&,^,<,>>,>>>(zero-fill right shift)
    • Logical operators: AND, OR
    • Comparison: =,!=,>,>=,<,<=,<>
    • String operator: ||(concatenate)
  • Math
    • ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN
  • Type Checking
    • IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE
  • String functions
    • CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER
  • Array functions
    • ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE

How to execute SQL Query?

  • Click on Query Explorer,
  • Select database
  • Select collection on which query needs to be executed
  • Click on Run Query

    SQL Server

Sample Data

For reference, I have created 2 documents under the Families collection.

Document 1

  1. {  
  2.     "id""KulkarniFamily",  
  3.     "lastName""Kulkarni",  
  4.     "location": {  
  5.         "city""Pune",  
  6.         "zipCode": 411014  
  7.     },  
  8.     "children": [{  
  9.         "firstName""Nirvi",  
  10.         "gender""female",  
  11.         "pets": [{  
  12.             "name""Moti",  
  13.             "type""Dog"  
  14.         }, {  
  15.             "name""Pussy",  
  16.             "type""Cat"  
  17.         }]  
  18.     }]  
  19. }  

Document 2

  1. {  
  2.     "id""DeshpandeFamily",  
  3.     "lastName""Deshpande",  
  4.     "location": {  
  5.         "city""Ahmednagar",  
  6.         "zipCode": 414003  
  7.     },  
  8.     "children": [{  
  9.         "firstName""Akshay",  
  10.         "gender""male",  
  11.         "pets": [{  
  12.             "name""Joe",  
  13.             "type""Dog"  
  14.         }, {  
  15.             "name""Mau",  
  16.             "type""Cat"  
  17.         }]  
  18.     }, {  
  19.         "firstName""Seeta",  
  20.         "gender""female",  
  21.         "pets": [{  
  22.             "name""Rocky",  
  23.             "type""Dog"  
  24.         }]  
  25.     }]  
  26. }  

Writing Simple Query

  1. Select * from c
    Return every document exactly stored in’ Families’ collection. Here, c refers to the current collection context. You can write anything instead of c.

  2. Select city and zip code.

    SQL Server

  3. Reduce the source to a subset of children (per family). Retrieve children's details only.

    SQL Server

  1. This query will throw an error as
    When we say select * from c.children, it means we are fetching the data of children only. The context will be limited to children only. As we are specifying location.city='Pune' in where clause, this query will fail as location is at root level and not at children level.

    SQL Server

  2. This query will run perfectly as we are setting the scope to the whole collection in from clause.

    SQL Server

  1. You can set context to subset entity by using in clause.

    SQL Server

  2. Select the first name of each child and the number of pets they have.

    SQL Server

    Above query selects first name and number of pets by calculating array length of pets We have used null coalescing (??) operator for setting the count to 0 if no pets are associated with the child.

  3. Select the last name, first name, and the number of pets each child has.

    SQL Server

    Here, we can find first name and number of pets from children entity but to find out the last name, we need to refer to the family entity. To select the last name along with the first name, we need to use join operator.

    In DocumentDB, join operator do not perform join between two documents but they perform the intra-document join. So, in above query, we have performed an intra-document join between family entity and children entity.

  4. Select Last Name, First Name, and Pet Name.

    SQL Server

    The above query performs nested join (collection <-> children <-> pets) to fetch lastName, firstName, and petName.

  5. Select values of pet names but exclude the property name.

    SQL Server

    In above query, the VALUE keyword is used to select values and to exclude property names.

  6. Concatenate firstName and lastName to form a fullName.

    SQL Server

    Here, ‘||’ operator is used to concatenate two strings.

  1. Concatenation using CONCAT function.

    SQL Server

    Alternately, concatenation can be performed using CONCAT function.