Monday 9 August 2021

ArangoDB: Query by example

‘byExample’ method fetches all the documents that matches to given collection and return a cursor.

 

Signature

collection.byExample(example)

 

Example

db.user.byExample({"age": 32}).toArray()

db.user.byExample({"age": 32, "lastName": "Gurram"}).toArray()

 

For example, user collection has following documents.

127.0.0.1:8529@abc_org> db.user.all().toArray()
[ 
  { 
    "_key" : "15950", 
    "_id" : "user/15950", 
    "_rev" : "_cReaqJC---", 
    "id" : 1, 
    "firstname" : "Sailu", 
    "lastName" : "Ptr", 
    "age" : 32 
  }, 
  { 
    "_key" : "15970", 
    "_id" : "user/15970", 
    "_rev" : "_cRebGD2---", 
    "id" : 2, 
    "firstname" : "Gopi", 
    "lastName" : "Battu", 
    "age" : 33 
  }, 
  { 
    "_key" : "15978", 
    "_id" : "user/15978", 
    "_rev" : "_cRebX7q---", 
    "id" : 3, 
    "firstname" : "Krishna", 
    "lastName" : "Gurram", 
    "age" : 32 
  }, 
  { 
    "_key" : "15998", 
    "_id" : "user/15998", 
    "_rev" : "_cRebx9K---", 
    "id" : 4, 
    "firstname" : "Venkat", 
    "lastName" : "Ptr", 
    "age" : 35 
  } 
]

 

Example 1: Get all the users whose age is 32.

127.0.0.1:8529@abc_org> db.user.byExample({"age": 32}).toArray()
[ 
  { 
    "_key" : "15950", 
    "_id" : "user/15950", 
    "_rev" : "_cReaqJC---", 
    "id" : 1, 
    "firstname" : "Sailu", 
    "lastName" : "Ptr", 
    "age" : 32 
  }, 
  { 
    "_key" : "15978", 
    "_id" : "user/15978", 
    "_rev" : "_cRebX7q---", 
    "id" : 3, 
    "firstname" : "Krishna", 
    "lastName" : "Gurram", 
    "age" : 32 
  } 
]

 

Example 2: Get all the users whose age is 32 and lastName is Gurram.

127.0.0.1:8529@abc_org> db.user.byExample({"age": 32, "lastName": "Gurram"}).toArray()
[ 
  { 
    "_key" : "15978", 
    "_id" : "user/15978", 
    "_rev" : "_cRebX7q---", 
    "id" : 3, 
    "firstname" : "Krishna", 
    "lastName" : "Gurram", 
    "age" : 32 
  } 
]

 

You can even provide an example on nested properties like below.

 

Example

{ "a.b" : 1 }

 

Find all the documents, which contain a sub-document in ‘a’ that has an attribute ‘b’ of value 1.

 

Let’s experiment with some more complex example.

 

127.0.0.1:8529@abc_org> db.test.all().toArray()
[ 
  { 
    "_key" : "16363", 
    "_id" : "test/16363", 
    "_rev" : "_cReka5S---", 
    "a" : 1, 
    "b" : { 
      "c" : 1, 
      "d" : 2 
    } 
  }, 
  { 
    "_key" : "16365", 
    "_id" : "test/16365", 
    "_rev" : "_cRekeRa---", 
    "a" : 1, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  }, 
  { 
    "_key" : "16373", 
    "_id" : "test/16373", 
    "_rev" : "_cReksIS---", 
    "a" : { 
      "b" : 1 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  }, 
  { 
    "_key" : "16387", 
    "_id" : "test/16387", 
    "_rev" : "_cRek37y---", 
    "a" : { 
      "b" : 1, 
      "c" : 2 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  } 
]

 

{ "a" : { "b" : 1 } } is not same as {"a.b" : 1}

 

{ "a" : { "b" : 1 } } find all documents, such that the attribute a contains a document of the form {b : 1 }. For example the document

 

{ "a" : {  "b" : 1 }, "b" : { "c" : 1, "d" : 1 } }

 

Will match, but the document.

 

{ "a" : { "b" : 1, "c" : 2 }, "b" : { "c" : 1, "d" : 1 } }

 

Will not match.

127.0.0.1:8529@abc_org> db.test.byExample({ "a" : { "b" : 1 } } ).toArray()
[ 
  { 
    "_key" : "16373", 
    "_id" : "test/16373", 
    "_rev" : "_cReksIS---", 
    "a" : { 
      "b" : 1 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  } 
]

 

But  if you use

 

{ "a.b" : 1 }

then you will find all documents, which contain a sub-document in a that has an attribute b of value 1.

127.0.0.1:8529@abc_org> db.test.byExample({ "a.b" : 1 } ).toArray()
[ 
  { 
    "_key" : "16373", 
    "_id" : "test/16373", 
    "_rev" : "_cReksIS---", 
    "a" : { 
      "b" : 1 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  }, 
  { 
    "_key" : "16387", 
    "_id" : "test/16387", 
    "_rev" : "_cRek37y---", 
    "a" : { 
      "b" : 1, 
      "c" : 2 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  } 
]

Alternative syntax

collection.byExample(path1, value1, ...)

 

Example1

127.0.0.1:8529@abc_org> db.test.byExample("a.b", 1 ).toArray()
[ 
  { 
    "_key" : "16373", 
    "_id" : "test/16373", 
    "_rev" : "_cReksIS---", 
    "a" : { 
      "b" : 1 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  }, 
  { 
    "_key" : "16387", 
    "_id" : "test/16387", 
    "_rev" : "_cRek37y---", 
    "a" : { 
      "b" : 1, 
      "c" : 2 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  } 
]

Example 2

127.0.0.1:8529@abc_org> db.test.byExample("a.b", 1, "a.c", 2 ).toArray()
[ 
  { 
    "_key" : "16387", 
    "_id" : "test/16387", 
    "_rev" : "_cRek37y---", 
    "a" : { 
      "b" : 1, 
      "c" : 2 
    }, 
    "b" : { 
      "c" : 1, 
      "d" : 1 
    } 
  } 
]



 

Previous                                                    Next                                                    Home

No comments:

Post a Comment