API – Creating Autopay Query Objects

General

For some Autopay data exports, query objects are required. Query objects tell the system how to filter the results that it sends back. This article will cover the basics of how to create a query object for Autopays. It assumes you are familiar with the items discussed in API – Autopay collections. You will send a POST request to the url, https://loanpro.simnang.com/api/public/api/1/Autopays/Autopal.Search()

Layout

The basic layout is as follows:

{
  "aggs": {
    ...aggs options...
  },
  "query":{
    ...query options...
  }
}

The “aggs” section (short for aggregates) will help the system to determine the basic summaries to return from a search. The “query” section will determine the filtering for the search.

Aggs

The “aggs” section is used mostly for displaying a summary search results in a UI. It doesn’t have any impact on a data export and will oftentimes be the same as the sample given in this article.

There are two sections of aggs.  The first is the avg_amount – this determines which field(s) will be averaged in the summary.  The second is the sum_amount – this determines which field(s) will be summed in the summary.

Below is an example:

"aggs":{
  "avg_amount": {
    "avg": {
      "field": "amount"
    }
  }
  "sum_amount": {
    "sum {
      "field": "amount"
    }
  }
}

In the above example, the summary will show both the average and sum of the “amount” field across all Autopays.

Query

The query section is used to filter the results. Filters are formed from several JSON objects that tell the system to follow a set of rules. The main JSON building block is the “bool” object.

The “bool” object has the following properties (both of which are optional)

  • must – This is an array of different requirements that an Autopay must meet to appear in the results
  • should – This is an array of different requirements that an Autopay should meet to appear in the results. Priority is given to the Autopays that meet this result; it is not guaranteed that Autopays that do not meet this requirement will neither be included nor excluded. Autopays that do meet this requirement will be included.

This building block will only check if something matches exactly. Each “must” and “should” property should be a list of objects with properties that are either bool objects or match objects.

Match objects have a single property; the property is the name of the Autopay field with a value of the desired Autopay field value.

Below is an example of all of this put together:

"query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "autopay.status.cancelled"
          }
        }
      ]
    }
  }

The above example will filter the query to only return cancelled Autopays.

Filter By Range

At times one wants to filter by a date range. When this happens, the Query section becomes more complex. In the above example, the query object contained only one child: a single bool object. However, once dates are added in, the query object will contain two children: a filtered object and a query object. The query object contains a bool object and is formatted as described in the above section.

There is only one field that can be filtered by a range for Autopays: the processed date time field. The filtered object has the following structure:

"filtered":{
  "filter":{
    "range":{
      "processDateTime":{
        "gte": <Date: YYYY-DD-MM 00:00:00>,
        "lte": <Date: YYYY-DD-MM 23:59:59>,
      }
    }
  }
 }
  • gte – (Optional) This is the bottom-most restriction for the date range; all dates must be greater than or equal to this one; replace <Date: YYYY-DD-MM 00:00:00> with the date formatted as YYYY-DD-MM 00:00:00
  • lte – (Optional) This is the upper-most restriction for the date range; all dates must be less than or equal to this one; replace <Date: YYYY-DD-MM 23:59:59> with the date formatted as YYYY-DD-MM 23:59:59

When all put together, the query section looks like the following:

  "query": {
    "filtered": {
      "filter": {
        "range": {
          "processDateTime": {
            "gte": "2015-02-08%2000:00:00",
            "lte": "2016-05-03%2023:59:59"
          }
        }
      },
      "query": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "match": {
                      "status": "autopay.status.pending"
                    }
                  },
                  {
                    "match": {
                      "status": "autopay.status.failed"
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }

Putting it All together

Below is a sample request for filtering Autopays to include only those of cancelled status.

{
  "aggs": {
    "avg_amount": {
      "avg": {
        "field": "amount"
      }
    },
    "sum_amount": {
      "sum": {
        "field": "amount"
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "autopay.status.cancelled"
          }
        }
      ]
    }
  }
}

Below is a sample request for filtering Autopays to include only those that will be processed between Aug 2, 2015 and March 5, 2016 and of any status:

{
  "aggs": {
    "avg_amount": {
      "avg": {
        "field": "amount"
      }
    },
    "sum_amount": {
      "sum": {
        "field": "amount"
      }
    }
  },
  "query": {
    "filtered": {
      "filter": {
        "range": {
          "processDateTime": {
            "gte": "2015-02-08%2000:00:00",
            "lte": "2016-05-03%2023:59:59"
          }
        }
      },
      "query": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "match": {
                      "status": "autopay.status.pending"
                    }
                  },
                  {
                    "match": {
                      "status": "autopay.status.failed"
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Related Articles

Leave A Comment?