TinyDB - The test() Query

TinyDB - The all() Query

For searching the fields containing a list, TinyDB provides a method called any(). This method matches at least one given value from the database. It finds either an entire list or a minimum one value as per the query provided.

Syntax

The syntax of TinyDB any() is as follows −

db.search(Query().field.any(query|list)

Here, field represents the part of data that we want to access. Query() is the object created of our JSON table named student.

If we will provide query as the argument of any() method, it will match all the documents where at least one document in the list field match the given query.
On the other hand, if we will provide list as the argument of any() method, it will match all the documents where at least one document in the list field is present in the given list.

Let's understand how it works with the help of a couple of examples. We will use the same student database that we have used in all the previous chapters.

Example 1

Let's see how we can find the fields from our student table where subject is either TinyDB, or MySQL, or SQL or combination of any two or three −

from tinydb import TinyDB, Query
db = TinyDB('student.json')
db.search(Query().subject.any(['TinyDB', 'MySQL', 'oracle']))

The above query will fetch all the rows where the "subject" field contains any of the following values: "TinyDB", "MySQL", or "oracle" −

[
   {
      "roll_number":2,
      "st_name":"Ram",
      "mark":[
         250,
         280
      ],
      "subject":[
         "TinyDB",
         "MySQL"
      ],
      "address":"delhi"
   },
   {
      "roll_number":3,
      "st_name":"kevin",
      "mark":[
         180,
         200
      ],
      "subject":[
         "oracle",
         "sql"
      ],
      "address":"keral"
   }
]

Example 2

Let's see how the any() method reacts when it doesn't match anything from the given list −

from tinydb import TinyDB, Query
db = TinyDB('student.json')
db.search(Query().subject.any(['Oracle']))

This query will return a blank value because there are no rows with its "subject" as "Oracle".

[]

Example 3

Observe that it is case-sensitive. The "subject" field does not have "Oracle", but it does have "oracle". Try the following query −

from tinydb import TinyDB, Query
db = TinyDB('student.json')
db.search(Query().subject.any(['oracle']))

It will fetch the following row −

[{
   'roll_number': 3,
   'st_name': 'kevin',
   'mark': [180, 200],
   'subject': ['oracle', 'sql'],
   'address': 'keral'
}]

As it is case-sensitive, it returned a blank value in the previous example because there are no rows with its "subject" as "Oracle".

TinyDB - The any() Query