Skip to main content

Query Documents

Tigris provides powerful query functionality for specifying which documents you want to retrieve. There is no need to index any field as Tigris allows querying on any field of a document.

Request Specification​

HTTP APIHTTP MethodURL ParamsResource
READPOST
  • Database Name
  • Collection Name
  • /databases/{db}/collections/{collection}/documents/read

    Request Payload​

    {
    "fields": {
    <field1>: 1|0,
    <field2>: 1|0,
    },
    "filter": {
    <field1>: number|string|boolean,
    <field2>: {<$lt|$lte|$gt|$gte>: number},
    <$and|$or>: [<field3>: number|string|boolean, expr1, expr2...]
    },
    "options": {
    "limit": number
    }
    }

    Understanding the request payload:

    • The fields projection allows you to specify which field to include by setting it to 1 or exclude it by setting it to 0. By default, all the fields are returned in the response.
    • The filter syntax is documented in the following section.
    • The options allows you to paginate the result.

    Filter specification​

    Deep diving into the JSON representation of the filter

    {
    "filter": {
    <field1>: number|string|boolean,
    <field2>: {<$lt|$lte|$gt|$gte>: number},
    <$and|$or>: [<field3>: number|string|boolean, expr1, expr2...]
    }
    }
    • On the left either you can have a field name or you can specify a logical operator. On the right, either you can have a value or an expression.
    • An expr inside logical operator can be a simple condition, or multiple conditions combined using nested logical operators.
    • For $eq: a matching value can be of type numeric, string or boolean.
    • For $lt, $lte, $gt, $gte: only numeric values are allowed.
    note

    Observe that when no comparison operator is passed then $eq is assumed. Similarly, unless explicitly provided $and is assumed on the top level conditions.

    Filter​

    Filter provides the following comparison operators with the same semantics as in virtually all programming languages.

    • $eq: equal to is used for exact matching.
    • $lt: less than is used for matching documents using less than criteria.
    • $lte: less than or equal to is similar to $lt but also matches for equality.
    • $gt: greater than is used for matching documents using greater than criteria.
    • $gte: greater than or equal to is similar to $gt but also matches for equality.

    For multiple conditions, there are two logical operators supported.

    • $or: Combines multiple filter operators and returns documents when either condition is met.
    • $and: Combines multiple filter operators and returns documents when all the conditions are met.

    Example Collection​

    The first step is to create the collection object.

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/createOrUpdate' \
    -X POST \
    -H 'Content-Type: application/json' \
    -d '{
    "schema":{
    "title":"catalog",
    "properties":{
    "id":{
    "type":"integer",
    "format":"int32",
    "autoGenerate":true
    },
    "name":{
    "type":"string"
    },
    "price":{
    "type":"number"
    },
    "brand":{
    "type":"string"
    },
    "labels":{
    "type":"string"
    },
    "popularity":{
    "type":"integer"
    },
    "reviews":{
    "type":"object",
    "properties":{
    "author":{
    "type":"string"
    },
    "rating":{
    "type":"number"
    }
    }
    }
    },
    "primary_key":[
    "id"
    ]
    }
    }'

    Assuming an e-commerce website that has the above collection catalog and has 5 products(documents) in it.

    idnamepricebrandlabelspopularityreviews
    1fiona handbag99.9michael korspurses8{"author": "alice", "rating": 7}
    2tote bag49coachhandbags9{"author": "olivia", "rating": 8.3}
    3sling bag75coachpurses9{"author": "alice", "rating": 9.2}
    4sneakers shoes40adidasshoes10{"author": "olivia", "rating": 9}
    5running shoes89nikeshoes10{"author": "olivia", "rating": 8.5}

    Simple read query​

    A straightforward query is to read document by applying a filter on a field. As an example, using filter in above collection to get a product where brand = "adidas"

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "brand": "adidas"
    },
    "options": {
    "limit": 1
    }
    }'

    Above query would return a single document matching the filter. To read multiple documents:

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "brand": "adidas"
    }
    }'

    Or to read all documents from the collection:

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {}
    }'
    tip

    By default, string comparison is case-sensitive. To learn, how to make case-insensitive queries, see the case-insensitive queries section.

    Filtering on multiple fields​

    Single field filtering is useful but what if you need to also filter by price. Following is an example where we are reading all the products where brand = "adidas" and price < 50

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "brand": "adidas",
    "price": {
    "$lt": 50
    }
    }
    }'

    Reading specific fields​

    Instead of reading all the fields of a document, fields projection allows reading specific fields. As an above example, let's say you only need to read name, price and brand fields from a document.

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "fields": {
    "name": 1,
    "price": 1,
    "brand": 1
    },
    "filter": {
    "brand": "adidas"
    }
    }'
    caution

    Users can either specify include fields or exclude fields, but not both.

    Applying range conditions​

    Many times the need for filtering is based on range on a numeric field. A range can be applied to any numeric field and in fact multiple numeric fields can be part of a single filter. Let’s take an example of reading all the products that are price less than 50 and have a popularity score of greater than or equal to 8.

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "price": {
    "$lt": 50
    },
    "popularity": {
    "$gte": 8
    }
    }
    }'

    Querying by metadata​

    Tigris automatically generates following metadata fields for the document:

    • created_at: Time when this document was added to database.
    • updated_at: Time when this document was last modified. This field is only generated once an existing document is modified.

    These generated fields are queryable by user. For example, to fetch documents inserted within a 24 hour period:

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "$and": [{
    "created_at":
    {
    "$gte": "2022-01-01T17:29:28.000Z"
    }
    },
    {
    "created_at":
    {
    "$lt": "2022-01-02T17:29:28.000Z"
    }
    }]
    }
    }'

    Applying multiple logical filters​

    Even after applying multiple AND conditions, what if you need something even more complex? What about reading documents where we need a logical OR on brand but also need to apply logical AND on some other fields. Let's read products where the brand is either "adidas" or "coach" but the price should be less than 50 and the product should be popular.

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "$or": [{
    "brand": "adidas"
    }, {
    "brand": "coach"
    }],
    "price": {
    "$lt": 50
    },
    "popularity": {
    "$gte": 8
    }
    }
    }'

    Querying nested fields​

    As we can see all the above examples are for top level fields but what if you have an object, and you want to filter documents based on one of the nested field. Taking the above data, if you want to get all the products which have labels set as "shoes" but should have rating greater than 7.

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "labels": "shoes",
    "reviews.rating": {
    "$gt": 7
    }
    }
    }'

    Case-insensitive queries​

    By default, all String comparisons are case-sensitive. However, if you need to ignore the case then set the case to ci in the collation object. The following example demonstrates a case-insensitive query for brand "Adidas"

    curl 'http://localhost:8081/v1/databases/catalogdb/collections/catalog/documents/read' \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{
    "filter": {
    "brand": "Adidas"
    },
    "options": {
    "collation": {
    "case": "ci"
    }
    }
    }'

    Above query will match terms ["adidas", "aDiDas", "Adidas", "adiDas"] etc.