Wednesday, 22 September 2021

ArangoDB: AQL: Sort the documents

Syntax

FOR doc IN collection
    SORT doc.property
    RETURN doc

 

Example 1: Get all the first names of users in ascending order.

queryToExecute = `FOR doc IN users
    SORT doc.firstName
    RETURN doc.firstName`

 

127.0.0.1:8529@abc_org> queryToExecute = `FOR doc IN users
...>     SORT doc.firstName
...>     RETURN doc.firstName`
FOR doc IN users
    SORT doc.firstName
    RETURN doc.firstName

127.0.0.1:8529@abc_org> db._query(queryToExecute)
[object ArangoQueryCursor, count: 6, cached: false, hasMore: false]

[ 
  "Gopi", 
  "Jessi", 
  "Joel", 
  "Madhu", 
  "Ram", 
  "Siva" 
]

 

Example 2: Get all the first names of users in descending order.

queryToExecute = `FOR doc IN users
    SORT doc.firstName DESC
    RETURN doc.firstName`

127.0.0.1:8529@abc_org> queryToExecute = `FOR doc IN users
...>     SORT doc.firstName DESC
...>     RETURN doc.firstName`
FOR doc IN users
    SORT doc.firstName DESC
    RETURN doc.firstName

127.0.0.1:8529@abc_org> db._query(queryToExecute)
[object ArangoQueryCursor, count: 6, cached: false, hasMore: false]

[ 
  "Siva", 
  "Ram", 
  "Madhu", 
  "Joel", 
  "Jessi", 
  "Gopi" 
]

 

Sort by multiple attributes

Syntax

FOR doc IN collection
    SORT doc.property1, doc.property2…
    RETURN doc

 

Example: Sort all the users with their age in ascending order and firstName in descending order.

queryToExecute = `FOR doc IN users
    SORT doc.age, doc.firstName DESC
    RETURN {"firstName" : doc.firstName, "age" : doc.age}`

 

127.0.0.1:8529@abc_org> queryToExecute = `FOR doc IN users
...>     SORT doc.age, doc.firstName DESC
...>     RETURN {"firstName" : doc.firstName, "age" : doc.age}`
FOR doc IN users
    SORT doc.age, doc.firstName DESC
    RETURN {"firstName" : doc.firstName, "age" : doc.age}

127.0.0.1:8529@abc_org> db._query(queryToExecute)
[object ArangoQueryCursor, count: 6, cached: false, hasMore: false]

[ 
  { 
    "firstName" : "Madhu", 
    "age" : null 
  }, 
  { 
    "firstName" : "Gopi", 
    "age" : null 
  }, 
  { 
    "firstName" : "Joel", 
    "age" : 34 
  }, 
  { 
    "firstName" : "Siva", 
    "age" : 35 
  }, 
  { 
    "firstName" : "Ram", 
    "age" : 35 
  }, 
  { 
    "firstName" : "Jessi", 
    "age" : 36 
  } 
]

As you see the output, some documents do not have any value with age attribute, we can filter them using FILTER operation.

queryToExecute = `FOR doc IN users
    FILTER doc.age
    SORT doc.age, doc.firstName DESC
    RETURN {"firstName" : doc.firstName, "age" : doc.age}`

127.0.0.1:8529@abc_org> queryToExecute = `FOR doc IN users
...>     FILTER doc.age
...>     SORT doc.age, doc.firstName DESC
...>     RETURN {"firstName" : doc.firstName, "age" : doc.age}`
FOR doc IN users
    FILTER doc.age
    SORT doc.age, doc.firstName DESC
    RETURN {"firstName" : doc.firstName, "age" : doc.age}

127.0.0.1:8529@abc_org> db._query(queryToExecute)
[object ArangoQueryCursor, count: 4, cached: false, hasMore: false]

[ 
  { 
    "firstName" : "Joel", 
    "age" : 34 
  }, 
  { 
    "firstName" : "Siva", 
    "age" : 35 
  }, 
  { 
    "firstName" : "Ram", 
    "age" : 35 
  }, 
  { 
    "firstName" : "Jessi", 
    "age" : 36 
  } 
]


You can add LIMIT operation to above query.

queryToExecute = `FOR doc IN users
    FILTER doc.age
    SORT doc.age, doc.firstName DESC
    LIMIT 2
    RETURN {"firstName" : doc.firstName, "age" : doc.age}`

127.0.0.1:8529@abc_org> queryToExecute = `FOR doc IN users
...>     FILTER doc.age
...>     SORT doc.age, doc.firstName DESC
...>     LIMIT 2
...>     RETURN {"firstName" : doc.firstName, "age" : doc.age}`
FOR doc IN users
    FILTER doc.age
    SORT doc.age, doc.firstName DESC
    LIMIT 2
    RETURN {"firstName" : doc.firstName, "age" : doc.age}

127.0.0.1:8529@abc_org> db._query(queryToExecute)
[object ArangoQueryCursor, count: 2, cached: false, hasMore: false]

[ 
  { 
    "firstName" : "Joel", 
    "age" : 34 
  }, 
  { 
    "firstName" : "Siva", 
    "age" : 35 
  } 
]



127.0.0.1:8529@abc_org>

 

 

Previous                                                    Next                                                    Home

No comments:

Post a Comment