Search

Search enables you to find objects in RaiseNow based on multiple criteria, allows you to sort and aggregate results.

Please note that the endpoint for API calls may differ based on the chosen object you'd like to search on. For instance, when dealing with payments, the endpoint could be Payment Search. Consult our API documentation for the respective endpoints.

info

Fair Use Policy

Please limit the amount of search queries you do to a sensible number. If you notice that responses are slow (e.g. because your search query is complex) make sure to reduce the number of requests you do. In case our monitoring notices unexpected or adversarial search behaviour impacting other API users, we may disable your API credentials without any prior warning.

Request

The search query follows a JSON-based structure of the following form:

  {
    "query": {
      //  Search query clauses
    },
    "sort": [
      // Sorting criteria
    ],
    "size": <int>, // the number of results to return
    "from": <int>, // from where to return the results
    "includes": [
      // Fields to include in response
    ],
    "excludes": [
      // Fields to exclude from response
    ],
    "aggs": {
      // Aggregation specifications
    }
  }

Response

When interacting with search queries, the response follows a structured format that encapsulates key information:

• summary.hits holds the count of discovered results.

• The hits section comprises the primary search outcomes.

• The aggregations section contains the list of requested aggregations.

This organized response structure provides comprehensive insights into search results, aggregations, and related details.

     {
        "summary": {
          "hits": <value>
        },
        "hits": [
          {
            <document1>
          },
          ...,
          {
            <documentN>
          }
        ],
      "aggregations": {
        "<aggregationName1>": [
          {
            "key": <"key1>,
            "doc_count": <x1>
          },
          ...,
          {
            "key": <"keyN>,
            "doc_count": <xN>
          }
        ],
        ...,
        "<aggregationNameN>": [...}
        }
      }

Query Structure

The query object contains a tree of clauses, which can be leaf clauses or compound clauses. Leaf clauses specify a search operation, while compound clauses combine other clauses with boolean operators.

Leaf Clause Structure

A leaf clause specifies an operator, the target field, and some parameters:

  {
    "$operator": {
      "field": { <parameters> }
    }
  }

Compound Clause Structure

A compound clause specifies an array of other clauses (leaf or compound) to which the boolean operator applies:

  {
    "$operator": [
      { <clause 1> },
      ...,
      { <clause N> }
    ]
  }

Operators are prefixed by $ to improve the readability of complex queries.

Term Query

A $term clause is used to search for a precise value (string, text, bool, null):

  {
    "$term": {
      "<field>": <value>
    }
  }

A $term clause looks for the full, precise value of a field. It considers spaces and capitalization. For example, given the document:

  {
    "comment": "the quick brown Fox"
  }

This query will match it:

  {
    "$term": {
      "comment": "the quick brown Fox"
      }
  }

But this will not match due to lowercase:

  {
    "$term": {
      "comment": "the quick brown fox"
    }
  }

And neither will match a substring like "brown Fox".

Terms Query

A $terms clause is used to search for any of several precise values (string, text, bool, null):

  {
    "$terms": {
      "<field>": [<value1>, ..., <valueN>]
      }
  }

It behaves like $term but looks for any of the specified values.

If used on a nested field, all values should be of the same type; otherwise, they will be cast to strings.

Range Query

  "query": {
      "$range": {
          "field": {
              "gte": <value>,
              "gt": <value>,
              "lte": <value>,
              "lt": <value>,
              "format" : <value>
          }
      }
  }

info

Using $range for Numeric or Date Ranges

The $range functionality is employed to define numeric or date ranges. When dealing with date ranges, the format parameter specifies the date format of the parameters.

It's worth noting that $range cannot be used with string fields. Furthermore, the lower/lower or equal (lt/lte) and greater/greater or equal (gt/gte) operations cannot coexist; at least one boundary must be defined.

Sorting

The sort parameter is used to specify the sorting criteria for documents in the API response. It enables you to determine the order in which the returned documents are presented.

The general format for utilizing the sort parameter is as follows:

{
  "sort": [
    {
      "field": <field>,
      "field_type": "string"|"numeric"|"boolean",
      "direction": "asc"|"desc"
    }
  ]
}

Whereas

• the field parameter specifies the sorting field.
• the field_type (exclusive to nested docs) can be "string", "numeric", or "boolean", defaulting to "string".
• direction sets sorting as "asc" (ascending) or "desc" (descending); default is "asc".
• multiple criteria in the sort array apply sequentially.

Example

To illustrate the use of the sort parameter, consider this example:

{
  "sort": [
    {
      "field": "example_parameters.analytics.suggested_amount",
      "field_type": "numeric",
      "direction": "asc"
    },
    {
      "field": "created",
      "direction": "desc"
    }
  ]
}

In this example, the documents are sorted by the example_parameters.analytics.suggested_amount field in ascending order, followed by the created field in descending order.

Size and From

• size: Specifies the maximum number of results to return; the default is 10.
• from: Specifies the offset of the first document, starting from 0. It is important to note that the from value must be less than the total number of records.

info

The combined values of size and from must not exceed 10,000 due to search limitations. You'll need to further qualify your query to reduce the amount of results returned.

Example:

  {
    "size": 100,
    "from": 300
  }

This example will return 100 documents starting from the 300th.

It is always advisable to specify size, especially for larger documents. When interested only in aggregation results, size is best set to 0.

Field Inclusion and Exclusion

You can customize the fields to include or exclude in the response using the following options:

• includes: Specifies which fields to return in the response; anything else will be omitted. If omitted or empty, all fields will be returned.
• excludes: Specifies which fields to exclude from the response; anything else will be returned.

These field selections can apply to both top-level and nested fields.

Examples

Include only uuid and organisation_uuid fields:

  {
    "includes": ["uuid", "organisation_uuid"]
  }

This example will return only the uuid and organisation_uuid fields.

Include all fields:

  {
    "includes": []
  }

This example will return all fields. The same result is achieved if omitted.

Exclude the custom_parameters field:

  {
    "excludes": ["custom_parameters"]
  }

Exclude specific fields while including all fields:

  {
    "includes": [],
    "excludes": ["custom_parameters"]
  }

This example will return all fields except custom_parameters.

Include specific fields while excluding a nested field:

  {
    "includes": ["uuid", "organisation_uuid", "custom_parameters"],
    "excludes": ["custom_parameters.analytics"]
  }

This example will return the top-level field uuid, all fields under custom_parameters, and all fields under custom_parameters.analytics, except for custom_parameters.analytics.google_tag_manager.

It is advisable to fetch only the relevant fields, especially when dealing with large documents.

Aggregations

Aggregations allow you to perform data summarization and analysis. Below is the format and explanation for using aggregations:

{
  "aggs": {
    "<aggregationName1>": {
      "type": "<type>",
      "field": "<field>",
      "<param1>": "<x>",
      ...,
      "<paramN>": "<x>",
      "stats": {
        "<statsName1>": "<field1>",
        ...,
        "<statsNameN>": "<fieldN>"
      }
    },
    ...,
    "<aggregationNameN>": {...}
  }
}

Aggregations are named, with their names serving as keys within the aggs object. The minimum specification for an aggregation includes its type, which defines the grouping method, and the field on which to perform the aggregation. Additional parameters might be required for specific aggregation types.

Response format

  {
    "aggregations": {
      "<aggregationName1>": [
        {
          "key": "<key1>",
          "doc_count": <x1>,
          "stats": {
            "<statsName1>": {
              "count": x,
              "min": y,
              "max": z,
              "avg": t,
              "sum": v
            },
            ...,
            "<statsNameN>": {...}
          }
        },
        ...,
        {
          "key": "<keyN>",
          "doc_count": <xN>
        }
      ],
      ...,
      "<aggregationNameN>": [...}
    }
  }

It is possible to use the stats parameter to define a list of fields on which to compute statistics (count, min, max, average, sum). All fields must be numeric. The statistics may be returned as integers or floating points, depending on the field mapping - the client should round them as suitable in the context.

Response format with stats

{
  "aggregations": {
    "<aggregationName1>": [
      {
        "key": <"key1>,
        "doc_count": <x1>,
        "stats": {
          "statsName1": {
            "count": x,
            "min": y,
            "max": z,
            "avg": t,
            "sum": v
          },
          ...,
          "statsNameN": {...}
        }
      },
      ...,
      {
        "key": <"keyN>,
        "doc_count": <xN>
      }
    ],
    ...,
    "<aggregationNameN>": [...}
      }
    }

Note that the field count in the statistics can differ from doc_count in case the stats field is not present in all documents of the bucket.

Terms Aggregation

The terms aggregation is used to aggregate data based on the values of a specific field. Here's how to structure it:

{
  "aggs": {
    "aggregationName": {
      "type": "terms",
      "field": "<field>",
      "field_type": "string"|"numeric"|"boolean"|"null",
      "size": x,
      "missing": <value>
    }
  }
}

A terms aggregation can be performed on any kind of field, including string, numeric, boolean, and null fields. When applied to string fields, the aggregation will be done on the precise original values.

For nested documents, the field_type parameter is mandatory.

The size parameter determines the number of keywords to return, presented in descending order based on popularity.

The missing parameter specifies the value to be used for documents that do not have the target field. If this parameter is not set, those documents will be ignored.

Example: Aggregating Donations by Top 5 Payment Methods

Consider the following aggregation query that aims to analyze donations based on the top 5 payment methods used, along with statistics related to the donation amounts:

{
  "aggs": {
    "donations_by_top_5_payment_method": {
      "type": "terms",
      "field": "payment_method",
      "size": 5,
      "stats": {
        "amount_stats": "amount"
      }
    }
  }
}

The corresponding response will be in the form of:

{
  "aggregations": {
    "donations_by_top_5_payment_method": [
      {
        "key": "card",
        "doc_count": 432283,
        "amount_stats_per_payment_method": {
          "count": 432283,
          "min": 1,
          "max": 800000,
          "avg": 3154.607828667794,
          "sum": 1363683336
        }
      },
      {
        "key": "twint",
        "doc_count": 8659,
        "amount_stats": {
          "count": 8659,
          "min": 41,
          "max": 5620039,
          "avg": 16751.630904261463,
          "sum": 145052372
        }
      }
    ]
  }
}

Range Aggregation

The range aggregation allows you to aggregate documents based on specified numeric ranges within a particular field. This can be useful for analyzing data that falls within specific value intervals.

{
  "aggs": {
    "aggregationName": {
      "type": "range",
      "field": "<field>",
      "ranges": [
        { "from": x1, "to": y1 },
        ...,
        { "from": xN, "to": yN }
      ]
    }
  }
}

from is optional for the first bucket, to for the last one.

Example: Payments Count in Range

The following query will aggregate the number of succeeded payments in two buckets, each being one week long:

{
    "query": {
        "$and": [
            {
                "$term": {
                    "last_status": "succeeded"
                }
            },
            {
                "$range": {
                    "created": {
                      "gt": "1693542753" 
                    }
                }
            }
        ]
    },
    "size": 0,
    "from": 0,
    "includes": [],
    "excludes": [],
    "sort": {
        "uuid": "DESC"
    },
    "aggs": {
      "myRange": {
        "type": "range",
        "field": "created",
        "ranges": [
          {"from": "1693542753", "to": "1694061153"},
          {"from": "1694061154", "to": "1694665953"}
        ]
      }
    }
}

whereas the response will be

{
  "summary": {
    "hits": 11268
  },
  "hits": [],
  "aggregations": {
    "myRange": [
      {
        "key": "1693542753-1694061153",
        "from": 1693542753000,
        "from_as_string": "1693542753",
        "to": 1694061153000,
        "to_as_string": "1694061153",
        "doc_count": 5711
      },
      {
        "key": "1694061154-1694665953",
        "from": 1694061154000,
        "from_as_string": "1694061154",
        "to": 1694665953000,
        "to_as_string": "1694665953",
        "doc_count": 5152
      }
    ]
  }
}

Histogram Aggregation

The histogram aggregation allows you to aggregate documents based on specified numeric intervals within a particular field. This can help you analyze the distribution of data across different value ranges.

  {
    "aggs": {
      "aggregationName": {
        "type": "histogram",
        "field": "<field>",
        "interval": x,
        "offset": x,
        "missing": x,
        "min_doc_count": x
      }
    }
  }

With

• interval being the interval based on which the buckets should be created.
• offset can be an offset from which the buckets should be created.
• missing defining how missing values should be treated.
• min_doc_count defining the minimum amount of documents for which buckets should be created.

Example: Payments Count by Amount

The following example shows the payment distribution based on the amount:

{
  "query": {
    "$and": [
      {
        "$term": {
          "last_status": "succeeded"
        }
      },
      {
        "$range": {
          "created": {
            "gt": "1693542753"
          }
        }
      }
    ]
  },
  "size": 0,
  "from": 0,
  "includes": [],
  "excludes": [],
  "sort": {
    "uuid": "DESC"
  },
  "aggs": {
    "aggregationName": {
      "type": "histogram",
      "field": "amount",
      "interval": 50000,
      "offset": 0,
      "missing": 0,
      "min_doc_count": 1
    }
  }
}

Whereas the response will be:

{
  "summary": {
    "hits": 11270
  },
  "hits": [],
  "aggregations": {
    "aggregationName": [
      {
        "key": 0,
        "doc_count": 11206
      },
      {
        "key": 50000,
        "doc_count": 39
      },
      {
        "key": 100000,
        "doc_count": 13
      },
      {
        "key": 150000,
        "doc_count": 2
      },
      {
        "key": 200000,
        "doc_count": 4
      },
      {
        "key": 250000,
        "doc_count": 2
      },
      {
        "key": 300000,
        "doc_count": 1
      },
      {
        "key": 500000,
        "doc_count": 1
      },
      {
        "key": 1000000000,
        "doc_count": 1
      },
      {
        "key": 2147450000,
        "doc_count": 1
      }
    ]
  }
}

Date Histogram Aggregation

The date_histogram aggregation allows you to aggregate documents based on a specified date field, grouping them into intervals based on time. This is particularly useful for analyzing data trends over time.

To use the date_histogram aggregation, follow this format:

  {
    "aggs": {
      "aggregationName": {
        "type": "date_histogram",
        "field": "<field>",
        "interval": x,
        "format": "date_format_string",
        "offset": x,
        "missing": x,
        "min_doc_count": x,
        "time_zone": "time_zone_string"
      }
    }
  }

Example: Payments Count per Day

The following will aggregate the number of payments based on an interval of 1 day:

{
  "query": {
    "$and": [
      {
        "$term": {
          "last_status": "succeeded"
        }
      },
      {
        "$range": {
          "created": {
            "gt": "1693542753"
          }
        }
      }
    ]
  },
  "size": 0,
  "from": 0,
  "includes": [],
  "excludes": [],
  "sort": {
    "uuid": "DESC"
  },
  "aggs": {
    "aggregationName": {
      "type": "date_histogram",
      "field": "created",
      "interval": "1d",
      "offset": 0,
      "missing": 0,
      "min_doc_count": 0
    }
  }
}

Whereas the response will be

{
  "summary": {
    "hits": 11272
  },
  "hits": [],
  "aggregations": {
    "aggregationName": [
      {
        "key_as_string": "1693526400",
        "key": 1693526400000,
        "doc_count": 904
      },
      {
        "key_as_string": "1693612800",
        "key": 1693612800000,
        "doc_count": 2527
      },
      {
        "key_as_string": "1693699200",
        "key": 1693699200000,
        "doc_count": 1255
      },
      {
        "key_as_string": "1693785600",
        "key": 1693785600000,
        "doc_count": 296
      },
      {
        "key_as_string": "1693872000",
        "key": 1693872000000,
        "doc_count": 296
      },
      {
        "key_as_string": "1693958400",
        "key": 1693958400000,
        "doc_count": 428
      },
      {
        "key_as_string": "1694044800",
        "key": 1694044800000,
        "doc_count": 419
      },
      {
        "key_as_string": "1694131200",
        "key": 1694131200000,
        "doc_count": 737
      },
      {
        "key_as_string": "1694217600",
        "key": 1694217600000,
        "doc_count": 2099
      },
      {
        "key_as_string": "1694304000",
        "key": 1694304000000,
        "doc_count": 989
      },
      {
        "key_as_string": "1694390400",
        "key": 1694390400000,
        "doc_count": 238
      },
      {
        "key_as_string": "1694476800",
        "key": 1694476800000,
        "doc_count": 345
      },
      {
        "key_as_string": "1694563200",
        "key": 1694563200000,
        "doc_count": 326
      },
      {
        "key_as_string": "1694649600",
        "key": 1694649600000,
        "doc_count": 397
      },
      {
        "key_as_string": "1694736000",
        "key": 1694736000000,
        "doc_count": 16
      }
    ]
  }