DocumentDB SQL - Value Keyword

When you know you're only returning a single value, then the VALUE keyword can help produce a leaner result set by avoiding the overhead of creating a full-blown object. The VALUE keyword provides a way to return JSON value.

Let’s take a look at a simple example.

Value KeyWord

Following is the query with VALUE keyword.

SELECT VALUE "Hello World, this is DocumentDB SQL Tutorial"

When this query is executed, it returns the scalar "Hello World, this is DocumentDB SQL Tutorial".

[ 
   "Hello World, this is DocumentDB SQL Tutorial" 
]

In another example, let’s consider the three documents from the previous examples.

Following is the AndersenFamily document.

{ 
   "id": "AndersenFamily", 
   "lastName": "Andersen", 
	
   "parents": [ 
      { "firstName": "Thomas", "relationship":  "father" }, 
      { "firstName": "Mary Kay", "relationship":  "mother" } 
   ],
   
   "children": [ 
      { 
         "firstName": "Henriette Thaulow", 
         "gender": "female", 
         "grade": 5, 
         "pets": [ { "givenName": "Fluffy", "type":  "Rabbit" } ] 
      } 
   ],
   
   "location": { "state": "WA", "county": "King", "city": "Seattle" }, 
   "isRegistered": true 
}

Following is the SmithFamily document.

{ 
   "id": "SmithFamily", 
	
   "parents": [ 
      { "familyName": "Smith", "givenName": "James" }, 
      { "familyName": "Curtis", "givenName": "Helen" } 
   ],
   
   "children": [ 
      { 
         "givenName": "Michelle", 
         "gender": "female", 
         "grade": 1 
      },
		
      { 
         "givenName": "John", 
         "gender": "male",
         "grade": 7, 
			
         "pets": [ 
            { "givenName": "Tweetie", "type": "Bird" } 
         ] 
      } 
   ],
   
   "location": { 
      "state": "NY", 
      "county": "Queens", 
      "city": "Forest Hills" 
   },
   
   "isRegistered": true 
}

Following is the WakefieldFamily document.

{ 
   "id": "WakefieldFamily",
	
   "parents": [ 
      { "familyName": "Wakefield", "givenName": "Robin" }, 
      { "familyName": "Miller", "givenName": "Ben" } 
   ],
   
   "children": [ 
      { 
         "familyName": "Merriam", 
         "givenName": "Jesse", 
         "gender": "female", 
         "grade": 6,
			
         "pets": [ 
            { "givenName": "Charlie Brown", "type": "Dog" }, 
            { "givenName": "Tiger", "type": "Cat" }, 
            { "givenName": "Princess", "type": "Cat" } 
         ] 
      },
		
      { 
         "familyName": "Miller", 
         "givenName": "Lisa", 
         "gender": "female",
         "grade": 3,
			
         "pets": [ 
            { "givenName": "Jake", "type": "Snake" } 
         ] 
      } 
   ], 
   
   "location": { "state": "NY", "county": "Manhattan", "city": "NY" }, 
   "isRegistered": false 
}

Following is the query.

SELECT VALUE f.location 
FROM Families f

When this query is executed, it return the returns the address without the location label.

[ 
   { 
      "state": "NY", 
      "county": "Manhattan", 
      "city": "NY" 
   }, 
	
   { 
      "state": "NY", 
      "county": "Queens", 
      "city": "Forest Hills" 
   },
	
   { 
      "state": "WA", 
      "county": "King", 
      "city": "Seattle" 
   } 
]

If we now specify the same query without VALUE Keyword, then it will return the address with location label. Following is the query.

SELECT f.location 
FROM Families f

When this query is executed, it produces the following output.

[ 
   { 
      "location": { 
         "state": "NY", 
         "county": "Manhattan", 
         "city": "NY" 
      } 
   }, 
	
   { 
      "location": { 
         "state": "NY", 
         "county": "Queens", 
         "city": "Forest Hills" 
      } 
   },
	
   { 
      "location": { 
         "state": "WA", 
         "county": "King", 
         "city": "Seattle" 
      } 
   } 
]
Advertisements