MindBridge Query Language

Overview

MindBridge Query Language (QL) is the standard unified query language used to interact with all the underlying data tables and collections within MindBridge. This query language has been extended to the MindBridge API, which uses this syntax for all /query endpoints within the API, as well as several other endpoints.

Syntax

The query is expressed as a JSON object. The example below looks for values equal to 10000 in the credit column.

{
  "credit": {
    "$eq": 10000
  }
}

Above, $eq is the equality operator. Other operators are listed below. As a shortcut for equality, you can specify the value directly.

{
  "credit": 10000
}

In order to conform to the syntax of a valid JSON object, all operators and fields must be enclosed in quotes. For more details on the JSON language, refer to json.org. Logical AND and OR conditions are available. If you want to specify two columns, the conditions can be combined with $and.

{
  "$and": [
    {
      "account": {
        "$eq": "1023345"
      }
    },
    {
      "risk": {
        "$gte": 5000
      }
    }
  ]
}

Simplified Syntax

The example $and query above can be simplified using the syntax seen below.

{
  "account": {
    "$eq": "1023345"
  },
  "risk": {
    "$gte": 5000
  }
}

You can combine $or and $and to build up a more complex structure, such as the one seen below, which combines all the techniques seen so far.

{
  "risk": {
    "$gte": 5000,
    "$lt": 7000
  },
  "$or": [
    {
      "transaction": {
        "$iprefix": "ABC1"
      }
    },
    {
      "transaction": {
        "$iprefix": "ABC2"
      }
    }
  ],
  "$and": [
    {
      "source": {
        "$ne": "MA"
      }
    },
    {
      "source": {
        "$iprefix": "M"
      }
    }
  ]
}

You can use two operators on the same column at the same time:

{
  "credit": {
    "$gte": 1000,
    "$lt": 10000
  }
}

Unique Names

Every field in a JSON object or sub-object must be unique. The following is not valid because source appears twice at the top level.

{
  "source": {
    "$ne": "MA"
  },
  "source": {
    "$ne": "MB"
  }
}

Instead, wrap it in $and:

{
  "$and": [
    {
      "source": {
        "$ne": "MA"
      }
    },
    {
      "source": {
        "$ne": "MB"
      }
    }
  ]
}

Or use another operator like $nin:

{
  "source": {
    "$nin": [
      "MA",
      "MB"
    ]
  }
}

You can use two operators on the same column at the same time:

{
  "credit": {
    "$gte": 1000,
    "$lt": 10000
  }
}

Column Operators

Column operators apply a filter to a specific column.

Description	Description	Column Types	Field Conditions
`$eq`	Tests if the value in the column is identical to the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `equalitySearch` must be true
`$ne`	Tests if the value in the column is not identical to the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `equalitySearch` must be true
`$gt`	Tests if the value in the column is greater than the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `rangeSearch` must be true
`$gte`	Tests if the value in the column is greater than or equal to the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `rangeSearch` must be true
`$lt`	Tests if the value in the column is less than the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `rangeSearch` must be true
`$lte`	Tests if the value in the column is less than or equal to the literal value.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `rangeSearch` must be true
`$contains`	Tests if an array contains a literal value. For example, given a transaction with entries from accounts 12345 and 23456, the following query on the `gl_journal_tx` data table would match the transaction: `{ "accounts": { "$contains": "12345" } }`	ARRAY_STRINGS	case-insensitive on STRING values `containsSearch` must be true
`$ncontains`	Tests if an array does not contain a literal value.	ARRAY_STRINGS	case-insensitive on STRING values `containsSearch` must be true
`$in`	Tests if a column is equal to one of the values in an array of literals.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `equalitySearch` must be true
`$nin`	Tests if a column is not equal to any values in an array of literals.	STRING DATE_TIME INT32 INT64 FLOAT32 FLOAT64 MONEY_100 PERCENTAGE_FIXED_POINT OBJECT_ID	case-insensitive on STRING values `equalitySearch` must be true
`$flags`	Accepts an object with one or more keys with boolean values. Tests if the flags (keys) match the values. For example, to search for entries that triggered the 2 Digit Benford control point, use this query on the `gl_journal_lines` data table. `{ "cp_failed": {"$flags": { "journal_entry_two_digit_benford": true }}}`	BOOLEAN_FLAGS
`$isubstr`	Tests if a literal value matches the value in the column. For example, the following query on the `engagements/query` endpoint will match engagements named “abc”, “aBc”, and “zabcd”. `{ "name": { "$isubstr": "abc" } }`	STRING	case-insensitive on STRING values `allowCaseInsensitiveSubstringSearch` must be true
`$iprefix`	Tests if a literal value matches the start of the value in the column. For example, the following query on the `gl_journal_tx` data table will match transactions “T1234”, “t1234”, and “T12345”. `{ "transaction": { "$iprefix": "T1234" } }`	STRING	case-insensitive on STRING values `caseInsensitivePrefixSearch` must be true
`$niprefix`	Tests if a literal value does not match the start of the value in the column.	STRING	case-insensitive on STRING values `caseInsensitivePrefixSearch` must be true

Root Operators

Logical Operators

Logical operators allow MindBridge to combine Column Operation queries to allow for more sophisticated calls.

Operator	Description
`$and`	Tests that all contained terms are evaluated to be `true`.
`$or`	Tests that any contained terms are evaluated to be `true`.

Keyword Operators

Keyword operators are applied simultaneously to all columns that support keyword searches. This is controlled by the keywordSearch attribute associated with the column’s metadata.

Operator	Description
`$keyword_prefix`	Performs a case-insensitive prefix search of all words in the row (operand must be a STRING). For example, the following query on the `gl_journal_tx` data table will return transactions with rows that contain the words abc , abcdef, or aBcd in any column. `{ "$keyword_prefix": "abc" }`
`$keyword_prefix_not`	Inverted case-insensitive prefix search (operand must be a STRING).

Population Operators

Population operators test whether the specified entry is or is not included within the specified population, identified by its ID. The population in question must be accessible from the analysis, meaning the population must be part of the analysis, engagement, or library that this data table resides in.

Operator	Description
`$population`	Tests that entries are part of the population specified by the provided ID.
`$not_population`	Tests that entries are not part of the population specified by the provided ID.

The correct usage of $population and $not_population is as follows, with 643eff00ec992f7ec42ed9f7 being a valid population ID:

{
  "$population": "643eff00ec992f7ec42ed9f7"
}

{
  "$not_population": "643eff00ec992f7ec42ed9f7"
}

Data Formats

Because the MindBridge QL is based on JSON, strings, numbers, and booleans are natively included in the language definition, but other values require some conversion. The following table describes the values MindBridge QL accepts in relation to our internal data structure. The contents of the “Column Type” column (below) represent the data types supported internally and how they are mapped to the JSON object structure.

Column Type	Format
`STRING`	Value must be a JSON string.
`DATE_TIME`	A JSON string in ISO8601 date-time format, such as 2019-08-10T00:50:00Z.
`BOOLEAN`	Value must be a JSON boolean (true or false).
`INT32`	Value must be a JSON number in the range [-2^31, 2^31-1].
`INT64`	Value must be a JSON number in the range [-2^63, 2^63-1].
`FLOAT32, FLOAT64`	Value must be a JSON number. FLOAT32 [1.2E-38, 3.4E+38] FLOAT64 [2.2E-308, 1.7E+308]
`MONEY_100`	Currently we express currency values as integers, essentially multiplying by 100 to store, and dividing by 100 when displaying the value. This allows MindBridge to operate with floating point numbers without loss of precision. Value must be a JSON number in the range [-2^63,2^63-1]. No division by 100 is performed on the actual data. For example, in a `MONEY_100` column, such as credit, the following query on the `gl_journal_lines` data table will find values greater than 1234. `{ "credit": { "$gt": 1234 } }`
`PERCENTAGE_FIXED_POINT`	Value must be a JSON number in the range [0, 10000] where 10000 means 100%.
`ARRAY_STRINGS`	Value must be an array of JSON strings.
`OBJECT_ID`	A 12-byte database identifier, represented by a 24-character hexadecimal string. Organizations, engagements, analyses, and analysis sources all use OBJECT_ID for their `id` fields. For example, `6686add55cd5c94147ecebdb`

Overview

Details

API reference

Overview

Syntax

Simplified Syntax

Unique Names

Column Operators

Root Operators

Logical Operators

Keyword Operators

Population Operators

Data Formats

Overview

Details

API reference

​Overview

​Syntax

​Simplified Syntax

​Unique Names

​Column Operators

​Root Operators

​Logical Operators

​Keyword Operators

​Population Operators

​Data Formats

Overview

Syntax

Simplified Syntax

Unique Names

Column Operators

Root Operators

Logical Operators

Keyword Operators

Population Operators

Data Formats