DocumentDB SQL - Urutan Berdasarkan Klausul

Microsoft Azure DocumentDB mendukung dokumen kueri menggunakan SQL melalui dokumen JSON. Anda dapat mengurutkan dokumen dalam koleksi pada angka dan string menggunakan klausa ORDER BY dalam kueri Anda. Klausa dapat menyertakan argumen ASC / DESC opsional untuk menentukan urutan pengambilan hasil.

Kami akan mempertimbangkan dokumen yang sama seperti pada contoh sebelumnya.

Berikut ini adalah AndersenFamily dokumen.

{ 
   "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 
}

Berikut ini adalah SmithFamily dokumen.

{ 
   "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 
}

Berikut ini adalah WakefieldFamily dokumen.

{ 
   "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 
}

Mari kita lihat contoh sederhana.

Berikut adalah query yang berisi kata kunci ORDER BY.

SELECT  f.id, f.children[0].givenName,f.children[0].grade  
FROM Families f  
ORDER BY f.children[0].grade

Ketika query di atas dijalankan, itu menghasilkan keluaran sebagai berikut.

[ 
   { 
      "id": "SmithFamily", 
      "givenName": "Michelle", 
      "grade": 1 
   },
	
   { 
      "id": "AndersenFamily", 
      "grade": 5 
   },
	
   { 
      "id": "WakefieldFamily", 
      "givenName": "Jesse", 
      "grade": 6 
   } 
]

Mari pertimbangkan contoh sederhana lainnya.

Berikut adalah kueri yang berisi kata kunci ORDER BY dan kata kunci opsional DESC.

SELECT f.id, f.parents[0].familyName 
FROM Families f  
ORDER BY f.parents[0].familyName DESC

Ketika query di atas dijalankan, maka akan menghasilkan keluaran sebagai berikut.

[ 
   {
      "id": "WakefieldFamily", 
      "familyName": "Wakefield" 
   },
	
   { 
      "id": "SmithFamily", 
      "familyName": "Smith" 
   },
	
   {
      "id": "AndersenFamily" 
   }
]