Query Predicates

All JSON queries accept some predicate, such as equals, not-equals, between and others, that modify or combine query elements and influence the data received on the results. On this section, you'll find SlicingDice's supported predicates and their usage.


Supported query predicates

Below you can find all SlicingDice supported query predicates used on JSON queries separated by each column type or query type. You can check if it is supported or not for each case.

Parameters Count entities Count events Aggregations Top Values Exists Total Result/Score
equals Supported Supported Supported Supported Unsupported Unsupported Supported
not equals Supported Supported Supported Supported Unsupported Unsupported Supported
between Supported Supported Supported Unsupported Unsupported Unsupported Supported
range Supported Supported Supported Unsupported Unsupported Unsupported Supported
greater than Supported Supported Supported Unsupported Unsupported Unsupported Supported
greater than or equal Supported Supported Supported Unsupported Unsupported Unsupported Supported
less than Supported Supported Supported Unsupported Unsupported Unsupported Supported
less than or equal Supported Supported Supported Unsupported Unsupported Unsupported Supported
starts with Supported Supported Supported Supported Unsupported Unsupported Supported
ends with Supported Supported Supported Supported Unsupported Unsupported Supported
contains Supported Supported Supported Supported Unsupported Unsupported Supported
not contains Supported Supported Supported Supported Unsupported Unsupported Supported
interval Unsupported Unsupported Supported Unsupported Unsupported Unsupported Unsupported
order Supported Supported Supported Supported Unsupported Unsupported Supported
Parameters String String event Integer Integer event Decimal Decimal event Boolean Date Datetime Unique-ID Geolocation Geolocation event
equals Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported
not equals Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported
between Unsupported Supported Unsupported Supported Unsupported Supported Unsupported Unsupported Unsupported Unsupported Unsupported Supported
range Unsupported Unsupported Supported Supported Supported Supported Unsupported Supported Supported Unsupported Supported Supported
greater than Unsupported Unsupported Supported Supported Supported Supported Unsupported Supported Supported Unsupported Supported Supported
greater than or equal Unsupported Unsupported Supported Supported Supported Supported Unsupported Supported Supported Unsupported Supported Supported
less than Unsupported Unsupported Supported Supported Supported Supported Unsupported Supported Supported Unsupported Supported Supported
less than or equal Unsupported Unsupported Supported Supported Supported Supported Unsupported Supported Supported Unsupported Supported Supported
starts with Supported Supported Supported Supported Supported Supported Supported Unsupported Unsupported Supported Supported Supported
ends with Supported Supported Supported Supported Supported Supported Supported Unsupported Unsupported Supported Supported Supported
contains Supported Supported Supported Supported Supported Supported Supported Unsupported Unsupported Supported Supported Supported
not contains Supported Supported Supported Supported Supported Supported Supported Unsupported Unsupported Supported Supported Supported
interval Unsupported Supported Unsupported Supported Unsupported Supported Unsupported Unsupported Unsupported Unsupported Unsupported Supported
order Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported Supported

Equals

The equals predicate should be placed inside a query element, as it selects entities whose columns match exactly the defined value.

{
    "query": [
        {
            "state": { "equals": "NY" }
        }
    ]
}

Not Equals

The not-equals predicate should be placed inside a query element, as it selects entities whose columns do not match the defined value.

{
    "query": [
        {
            "state": { "not-equals": "NY" }
        }
    ]
}

Between

The between predicate should be placed inside a query element, as it selects entities whose event columns have values within the given time interval, being either absolute or relative ones. The interval taken into account includes the beginning time but not the last one.

{
    "query": [{
        "purchases": {
            "equals": "computer",
            "between": [
                "2019-01-01T00:00:00Z",
                "2019-04-20T00:00:00Z"
            ]
        }
    }]
}

When defining intervals within the between interval, you can explicitly define the initial and final times in the ISO 8601 format: YYYY-MM-DDThh:mm:ssTZ.

You are also able to configure a timezone by replacing TZ by +HH:MM or -HH:MM. Example: 2019-05-28T10:35:23+03:00 or 2019-05-28T10:35:23-07:00. Using Z is a shortcut to using UTC, such as in 2019-05-28T10:35:23Z. However, it is important to know that the database will always store and fetch data in UTC format.

Relative intervals on between

You can also use the between predicate with relative intervals, where initial or final boundaries are displacements according to an absolute or the current date and time. Example: Entities whose purchases of product 1 happened in the past 5 hours

{
    "query": [
        {
            "purchases": {
                "equals": "product 1",
                "between": [
                    "now",
                    "-5 hours"
                ]
            }
        }
    ]
}

In the example above, now will be always replaced by the current date and time on servers. -5 hours will take exactly 5 hours before the current date and time.

You can apply the same logical for the following key words:

  • y or year. epresenting years. Example: -1y
  • m or months: representing months. Example: -3m
  • w or weeks: representing weeks. Example: +2w
  • d or days: representing days. Example: -7d
  • hr or hours: representing hours. Example: +12hr
  • min or minutes: representing minutes. Example: -15min
  • sec or seconds: representing seconds. Example: +30sec
  • now: Current date and time in the servers, in UTC.
  • today: Current date in the servers, from 00:00:00 of the current day to 00:00:00 of the next day, in UTC.
  • yesterday: Previous date in the database servers, from 00:00:00 of the previous day to 00:00:00 of the current day, in UTC.

Range

The range predicate should be placed inside a query element, as it selects entities whose numeric, date or datetime columns have values inside the given interval. The interval taken into account includes the initial value but not the last one.

{
    "query": [
        {
            "purchase-value": {
                "range": [ 550, 1150 ]
            }
        }
    ]
}

Greater than

The greater-than or gt predicate should be placed inside a query element, as it selects entities whose columns have values greater than the given number, datetime or date.

{
    "query": [
        {
            "purchase-value": { "gt": 550 }
        }
    ]
}

Greater than or equals

A greater-than-or-equal or gte predicate should be placed inside a query element, as it selects entities whose columns have values greater or equal than the given number, datetime or date.

{
    "query": [
        {
            "purchase-value": { "gte": 550.47 }
        }
    ]
}

Less than

A less-than or lt predicate should be placed inside a query element, as it selects entities whose columns have values less than the given number, datetime or date.

{
    "query": [
        {
            "purchase-date": { "lt": "2019-01-01T00:00:00Z" }
        }
    ]
}

Less than or equals

A less-than-or-equal or lte predicate should be placed inside a query element, as it selects entities whose columns have values less or equal than the given number, datetime or date.

{
    "query": [
        {
            "purchase-value": { "lte": 5000.10 }
        }
    ]
}

Starts with

The starts-with predicate should be placed inside a query element, as it selects entities whose values for a column start with a given string.

{
    "query": [
        {
            "state": { "starts-with": "new" }
        }
    ]
}

Ends with

The ends-with predicate should be placed inside a query element, as it selects entities whose values for a column ends with a given string.

{
    "query": [
        {
            "state": { "ends-with": "york" }
        }
    ]
}

Contains

The contains predicate should be placed inside a query element, as it selects entities whose values for a column contains a given string.

{
    "query": [
        {
            "state": { "contains": "new" }
        }
    ]
}

Not contains

The not-contains predicate should be placed inside a query element, as it selects entities whose values for a column not contains a given string.

{
    "query": [
        {
            "state": { "not-contains": "new" }
        }
    ]
}

Interval

An interval predicate should be placed inside a date histogram aggregation using event columns, as it defines how the data will be grouped. The following values are accepted:

  • months: Group data by months.
  • weeks: Group data by days.
  • days: Group data by weeks.
  • hours: Group data by hours. This interval can be used only for data in the past 30 days, or it'll produce errors.
  • minutes: Group data by minutes. This interval can be used only for data in the past 24 hours, or it'll produce errors.
  • seconds: Group data by seconds. This interval can be used only for data in the past 24 hours, or it'll produce errors.
{
    "query": [
        {
            "purchase-value": "avg",
            "interval": "days",
            "between": [
                "2019-05-29T00:00:00Z",
                "-30d"
            ]
        }
    ]
}

Order

The order predicate should be placed inside a query element, as it orders the column's values on an ascending or descending order.

{
    "query": [
        {
             "city": 5,
            "order": {"value": "desc"}
        }
    ]
}
Order types

On aggregation queries you can specify if you want to order values by quantity or by value.

Order by quantity

If you want your result sorted by the number of times the value was indexed you can use the order by quantity. The original aggregation query is already sorted by quantity or descending order, but you can reverse this result specifying the order by quantity on ascending order.

{
    "query": [
        {
             "city": 5,
            "order": {"quantity": "asc"}
        }
    ]
}
Order by value

If you want your result sorted by the actual value of the column, like a simple ORDER BY on SQL, you just need to specify an order by value using asc or desc as parameter.

{
    "query": [
        {
             "city": 5,
            "order": {"value": "asc"}
        }
    ]
}