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
  
  
  

Sample Data

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

Document 1

{
    "id": "KulkarniFamily",
    "lastName": "Kulkarni",
    "location": {
        "city": "Pune",
        "zipCode": 411014
    },
    "children": [{
        "firstName": "Nirvi",
        "gender": "female",
        "pets": [{
            "name": "Moti",
            "type": "Dog"
        }, {
            "name": "Pussy",
            "type": "Cat"
        }]
    }]
}

Document 2

{
    "id": "DeshpandeFamily",
    "lastName": "Deshpande",
    "location": {
        "city": "Ahmednagar",
        "zipCode": 414003
    },
    "children": [{
        "firstName": "Akshay",
        "gender": "male",
        "pets": [{
            "name": "Joe",
            "type": "Dog"
        }, {
            "name": "Mau",
            "type": "Cat"
        }]
    }, {
        "firstName": "Seeta",
        "gender": "female",
        "pets": [{
            "name": "Rocky",
            "type": "Dog"
        }]
    }]
}

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.
   
   
   
   
3. Reduce the source to a subset of children (per family). Retrieve children's details only.
   
   
   

4. 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.
   
   
   
   
5. This query will run perfectly as we are setting the scope to the whole collection in from clause.
   
   
   

6. You can set context to subset entity by using in clause.
   
   
   
   
7. Select the first name of each child and the number of pets they have.
   
   
   
   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.
   
   
8. Select the last name, first name, and the number of pets each child has.
   
   
   
   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.
   
   
9. Select Last Name, First Name, and Pet Name.
   
   
   
   The above query performs nested join (collection <-> children <-> pets) to fetch lastName, firstName, and petName.
   
   
10. Select values of pet names but exclude the property name.
    
    
    
    In above query, the VALUE keyword is used to select values and to exclude property names.
    
    
11. Concatenate firstName and lastName to form a fullName.
    
    
    
    Here, ‘||’ operator is used to concatenate two strings.

12. Concatenation using CONCAT function.
    
    
    
    Alternately, concatenation can be performed using CONCAT function.