API – Query Objects

Introduction

LoanPro uses Elasticsearch to perform a large portion of searches. Thus, the queries used by LoanPro closely resemble the queries used by Elasticsearch. This article explains how to use the query objects found throughout the LoanPro API. However, we recommend referencing Elasticsearch's documentation for greater details on the subject.

By default, queries only return the first ten results. However, this can be overridden by using the $top and $tart variables as detailed in the article API – Paginating Results.

Basic Format

The format of the query objects are as follows:

"query": {
        "query": {
            <query data>
        }
    }

It may seem redundant to have a “query” object inside of a “query” object, but this allows the LoanPro API to easily integrate with the power of Elasticsearch by sending all contents of the outer “query” object directly to Elasticsearch. Formatting query objects this way also allows the LoanPro API to expand upon new features and updates added to Elasticsearch.

The inner query object is the Elasticsearch query context; everything inside of this object needs to be formatted according to the Elasticsearch documentation. Inside this inner object is where you can place multiple queries. We recommend referencing Elasticsearch's documentation for greater details on the topic.

Results from queries are assigned a score and the score is used to determine the rankings. LoanPro will automatically filter out rankings that are too low to be considered useful. However, most queries should be designed to match or not match; only very particular circumstances call for a “sort of match” when dealing with loans. (For example, if you’re looking for loans with an APR of 5%, you don’t want to get loans with an APR of 4.9% even though the difference is very small.)

Make sure to pay close attention to the queries as you make them. They must be valid JSON, and if the JSON becomes invalid, the behavior of the API becomes undefined (usually it will return results you didn’t want, but that’s not a guarantee).

Due to the complexity of Elasticsearch and the nature of loan data, only a handful of Elasticsearch features are officially supported by LoanPro. Only officially supported features have a guarantee of working functionality. As a result, we strongly recommend you only use the officially supported features. However, all features are mentioned below for completeness.

Query Types

Below is a list of the basic query types:

  • match –  determines whether the specified field matches the provided value (for text, it sees if all the words in the value match any word in the specified field)
  • multi_match – determines whether the specified fields match the provided value
  • common – performs a preliminary query with less-commonly used words and then an adjustment with more commonly used words.
    • For example: the query, “The brown fox”, will first perform a query for “brown fox”; and then after it receives the results, it will perform an adjustment query (on the first query’s results) for “the”.
  • query_string –  uses a query parser that exists inside of Elasticsearch to perform a query
  • simple_query_string – also uses a query parser that exists inside of Elasticsearch to perform a query

The fields to match against are the same fields that exist in a loan. Due to most of those being numerical, the “match” query type will suffice for almost all queries.

Compound Queries

Compound queries are used to combine multiple queries and even provide logical operations. This allows for performing queries such as “Find all loans where the Loan Status is Active but the Loan Sub Status is not Recently Activated”. Each compound query is composed of clauses or collections of either more compound queries or of the basic query types. All clauses are evaluated from the innermost layers to the outermost layers with results being brought out. This can be thought of as parentheses in math equations: the inner-most nested parentheses are evaluated first, and their results are used in the surrounding equation.

Again, due to the structure of the data that will be searchable, only a small part of compound queries are officially supported. The supported compound queries will be marked as officially supported. All of them are mentioned here for completeness.

  • constant_score – returns a score modifier for every match
  • bool – Officially supported by LoanPro; returns whether a match was made based off one or more boolean clauses. The clauses are as follows:
    • must – The clause must be matched
    • filter – Restricts results to match the clause
    • should – The clause should be matched; unless it is the only clause type than all inside queries are optional, but the higher the match the better the ranking
    • must_not – the clause must not match
  • dis_max – performs two or more queries and then combines the matching documents. Ex. “Get all loans with an APR of 5% or an APR of 10%”
  • function_score – applies a score modifier to matches based off of some function
  • boosting – applies a score modifier to matches
  • indices – allows performing two different queries based on indices
  • and – matches documents by using the AND boolean operator
  • not – matches documents by using the NOT boolean operator
  • or – matches documents by using the OR boolean operator
  • filtered – deprecated. Replaced with the bool operator
  • limit -deprecated

Almost all of the queries ran by LoanPro use the “bool” compound query and its child clauses exclusively. By nesting bool queries and match queries, almost every query can be replicated. This can be proven in boolean arithmetic.

Proving the Implementation

Since the output of a bool compound query is either True or False, it can be simplified to the output of a boolean operation. Variables (such as A,B,C,D,etc) will be used to represent the test of a loan against a query (ex. A means “does the loan match query A”). Due to AND, OR, and NOT being the basic gates from which all other gates can emerge, and OR can be derived from AND and NOT using DeMorgan’s Law (which gives NOT(NOT A AND NOT B)), we can thereby prove that as long as we can perform a NOT operation and an AND operation we can thereby perform the needed series of operations to do any “full” match (where something either does or doesn’t match, not “kind of matches”; this will suffice for almost all searches).

Since the must will ensure that all sub-clauses match, it can be simplified into an AND operation. Therefore, by having two or more “match” clauses nested inside a “must” clause we have created our AND operation. The next step is to have a NOT operation. Bool queries provide a “must_not” clause which carries a NOT operation into all clauses. This means that any “match” clause nested inside becomes “must not match” and is thereby negated, proving we have a NOT operation. Since we have previously proved that we can get an OR operation from both AND and NOT operations, we have now proven that by just supporting the “bool” and “match” queries we can perform any query that does “full” matching, which is more than sufficient for loans. Please note that this is not the same as “exact” matching where text searched for has to 100% the data; when searching for Elasticsearch does break the words apart and searches for the existence of words in the data.

Making a Query

Below is a sample query that will pull all loans that are not active and do not have a "loanStatusId" of 2. (If a loan only meets one of those criteria, it will still be pulled.)

    "query": {
        "query": {
            "bool": {
                "must_not":
                [
                    {
                        "bool":
                        {
                            "must":
                            [
                                {
                                    "match":
                                    {
                                        "loanStatusId": "2"
                                    }
                                },
                                {
                                    "match": {
                                        "active": "1"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        }
    }

Here's a breakdown of the basic parts of the query object:

  • bool – this says that any result must match the series of queries contained inside
    • must_not – this says that results must not match the contained items
      • bool – any results must not match what what the queries contain inside
        • must – results to exclude must match all of the following parameters
          • match:loanStatusId – results to exclude must have their loan status id be 2
          • match:active – results to exclude must be for active loans

In English, the query says “Return all results that do not have BOTH a loan status id of 2 AND that are active”.

It can also be written with boolean math as:  ( NOT ( loanStatusId = 2 AND active = 1 ) ).

Composing a Query

We will now discuss how to compose a query. The very first step is to state, in very simple, clear, and precise English, what you want. For example, to pull a query giving all loans that have a loan status id of 4 or that are not active, we would say “Return all results that have EITHER a loan status id of 4 OR are not active”. We will then translate this to boolean math as follows:

  • Return all results that have – this becomes a set a parenthesis
  • EITHER – This indicates that we will be doing an OR in the future
  • a loan status of 4 – this is a constraint we’re going to use
  • OR – we will be placing an OR here
  • are not active – We could split this into a series of query elements, but the best option is have it be a constraint (active = 0)

We will now write it out

(loanStatusId = 4 OR active = 0)

Now, we need to figure out what to do about the OR. We could use DeMorgan’s law, or we could take advantage of the “should” part of a bool (remember that as long as a “should” is alone, at least one of the queries need to be matched, thus making it act like an OR).

We start off with our query body:

query:{}

Next, we add in a bool for the parenthesis:

query:{bool:{}}

Since we’re doing an OR, we’ll add in a “should” to the bool:

query:{bool:{should:[]}}

Next, we need to add the loanStatusId constraint to the should

query:{
   bool:{
      should:[
         {
            match:{
               "loanStatusId":4
            }
         }
      ]
   }
}

Next, we’ll add the active constraint:

query:{
   bool:{
      should:[
         {
            match:{
               "loanStatusId":4
            },
match:{
"active":0
}
         }
      ]
   }
}

Congratulations! You have built your first query!

StackBlitz Example

What is StackBlitz?
Many of our articles use StackBlitz examples like this. These examples are like a window into a REST client right here in your browser. The window is divided into two sides. Developers can use info in the editor on the left side when configuring your API integration, and the right side shows a preview of a REST client. Click 'Preview' on the bottom, and you'll only see the REST client.

The entire request is set up and connected to a demo API tenant in LMS. Just scroll down and click 'send' and you should get a 200 response. If you want to get some practice with error codes, try editing the endpoint, headers, method and payload to see what responses you get.

Next Steps

Now that you've learned a bit about queries and built one, head to the following articles to learn some more.

API – Query Object (Strings) explains how to query strings.

API – Query Objects (Nested) explains how to query nested entities.


How did we do?


Powered by HelpDocs (opens in a new tab)