Skip to main content
Version: 19

Metadata filters

What is a metadata filter?​

The metadata filters can be considered as a sub-filter of other filters. Other filters, like the dossier and party filters, support a list of metadata filters. A metadata filter represents a condition (=equals/greater than/like/…) for a single metadata field. It is possible to configure multiple metadata filters for the same metadata field should this be required.

In the Harmoney application, “metadata” fields are technically a map (=dictionary) stored in a JSON column in the database. Because of this, we cannot query these metadata fields the same way we query other fields. Instead, to filter on metadata fields we make use of the MySQL JSON functions.

Structure​

Each metadata filter consists of the following parts:

Metadata field key​

The filter must contain a key field containing a string value that corresponds with the metadata field name (not the WL translation!). This key should point to the correct JSON node in the JSON document.

A metadata field used as a filter has no label in the UI filter panel until it is declared in the FSP's field-definitions configuration; see filter field labels.

Some constraints apply to the metadata field key:

  • The key cannot be NULL.
  • It must match with the following regular expression: ^[a-zA-Z0-9]+$. Meaning that the key must contain atleast one character and can only contain letters, numbers and underscores. No other special characters, spaces or punctuation are allowed.

These constraints must be enforced because under the hood, the key is used as the path argument in the JSON functions that we use to filter on the metadata JSON. Invalid paths would result in MySQL errors and thus lead to internal server errors in the application.

For more info on JSON paths, see JSON path syntax.

Metadata field value​

The filter must contain a value field representing the value against which the condition on the metadata field will be evaluated. This value can generally be any data type, though some operators only allow for values of specific types. The value can also be NULL! See each operator for more details about what data types are allowed.

Metadata operator​

Each filter must contain an operator field with a MetadataOperator enum value. The operator specifies what type of condition should be evaluated against the metadata field.

The following metadata operators are currently supported:

EQUALS​

A filter with an equals operator will evaluate to true when the metadata filter value is the same as the value found in the JSON document at the specified JSON path.

Alternatively, this operator can also be used as an IS NULL condition by providing null as the metadata filter value. In this case, the filter will evaluate to true if the data found in the JSON document at the specified JSON path is null.

Example of the resulting SQL condition when using an EQUALS operator:

JSON_VALUE(d1_0.metadata, '$.foo')='"bar"'

Example of the resulting SQL condition when using an EQUALS operator and the filter value is null:

JSON_VALUE(d1_0.metadata, '$.foo') is null

NOT_EQUALS​

A filter with a NOT_EQUALS operator will evaluate to true when the metadata filter value is not equal to the value found in the JSON document at the specified JSON path.

When the filter value is non-null, missing/null metadata values are also considered a match. In other words, this operator now behaves as NOT EQUALS OR IS NULL for metadata keys.

When the filter value itself is null, this operator behaves as an IS NOT NULL condition.

Example of the resulting SQL condition when using a NOT_EQUALS operator and a non-null filter value:

JSON_VALUE(d1_0.metadata, '$.foo') <> '"bar"'
or JSON_VALUE(d1_0.metadata, '$.foo') is null

Example of the resulting SQL condition when using a NOT_EQUALS operator and the filter value is null:

JSON_VALUE(d1_0.metadata, '$.foo') is not null

LIKE​

Filters using the “like” operator only allow filter values of type String. The filter value will always be surrounded by wildcards! (e.g. "%filtervalue%")

A filter with a “like” operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path matches the pattern of the filter value surrounded by wildcards on both sides.

Example of the resulting SQL condition when using a LIKE operator:

cast(JSON_VALUE(d1_0.metadata, '$.foo') as char) like '%a%' escape ''

NOT_LIKE​

Filters using the NOT_LIKE operator only allow filter values of type String. The filter value will always be surrounded by wildcards (for example "%filtervalue%").

A filter with a NOT_LIKE operator will evaluate to true when the JSON value found at the specified path does not match the wildcard pattern.

When the filter value is non-null, missing/null metadata values are also considered a match (NOT LIKE OR IS NULL).

Example of the resulting SQL condition when using a NOT_LIKE operator:

cast(JSON_VALUE(d1_0.metadata, '$.foo') as char) not like '%a%' escape ''
or JSON_VALUE(d1_0.metadata, '$.foo') is null

IN​

Filters using the “in” operator only allow a collection as filter value. This can be a list of strings, integers, dates, etc.

A filter with the “in” operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path is within the provided filter collection of values.

Example of the resulting SQL condition when using a IN operator:

JSON_VALUE(d1_0.metadata, '$.foo') in ('123', 'abc', 'bar')

NOT_IN​

Filters using the NOT_IN operator only allow a collection as filter value. This can be a list of strings, integers, dates, etc.

A filter with the NOT_IN operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path is not within the provided filter collection of values.

When the filter value collection is non-empty, missing/null metadata values are also considered a match (NOT IN OR IS NULL).

Example of the resulting SQL condition when using a NOT_IN operator:

JSON_VALUE(d1_0.metadata, '$.foo') not in ('123', 'abc', 'bar')
or JSON_VALUE(d1_0.metadata, '$.foo') is null

BEFORE​

Filters using the “before” operator only allows Localdate and Instant data types as filter value.

A filter with the “before” operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path is not null, a valid date and the JSON value converted to a date is lesser than the filter date value.

Mixing Localdate and Instant types is currently NOT supported! Though it may probably work already, we cannot be sure that the results will always be correct since we don’t account for timezones.

Example of the resulting SQL condition when using a BEFORE operator with a date:

STR_TO_DATE(JSON_UNQUOTE(JSON_VALUE(d1_0.metadata, '$.foo')), '%Y-%m-%d')<2024-11-20

Example of the resulting SQL condition when using a BEFORE operator with a date time:

STR_TO_DATE(JSON_UNQUOTE(JSON_VALUE(d1_0.metadata, '$.foo')), '%Y-%m-%dT%H:%i:%s')<2024-11-20T00:00:00Z

AFTER​

Filters using the “after” operator only allows Localdate and Instant data types as filter value.

A filter with the “after” operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path is not null, a valid date and the JSON value converted to a date is greater than the filter date value.

Mixing Localdate and Instant types is currently NOT supported! Though it may probably work already, we cannot be sure that the results will always be correct since we don’t account for timezones.

Example of the resulting SQL condition when using a AFTER operator with a date:

STR_TO_DATE(JSON_UNQUOTE(JSON_VALUE(d1_0.metadata, '$.foo')), '%Y-%m-%d')>2024-11-20

Example of the resulting SQL condition when using a AFTER operator with a date time:

STR_TO_DATE(JSON_UNQUOTE(JSON_VALUE(d1_0.metadata, '$.foo')), '%Y-%m-%dT%H:%i:%s')>2024-11-20T00:00:00Z

RELATIVE_DATE​

Filters using the “relative date” operator only allow Strings that can be parsed as a valid “relative date time” as filter value. See Relative Datetime for more info on relative date times!

A filter with the “relative” operator will evaluate to true when the JSON value found in the JSON document at the specified JSON path is not null, a valid date and the JSON value converted to a date is, depending on the relative date time direction, greater or lesser than the parsed relative date time filter date value.

JSON schema​

Settings Configuration

Examples​

EQUALS​

A party filter that filters for parties that have value “loyal_customer” for metadata field “EXTRA_PARTY_STATUS”​
{
    "metadataFilters": [
        {
            "filterKey": "EXTRA_PARTY_STATUS",
            "filterValue": "loyal_customer",
            "operator": "EQUALS"
        }
    ]
}

IN​

A dossier filter that filters for dossiers that have either values “ONGOING”, “MIGRATED”, “CLOSED” for metadata field “METADATA_DOSSIER_LIFECYCLE”​
{
    "metadataFilters": [
        {
            "filterKey": "METADATA_DOSSIER_LIFECYCLE",
            "filterValue": ["ONGOING", "MIGRATED", "CLOSED"],
            "operator": "IN"
        }
    ]
}

BEFORE​

A dossier filter that filters for dossiers that have a date before 30th december 2023 for metadata field “DOSSIER_SPECIAL_DATE”​
{
    "metadataFilters": [
        {
            "filterKey": "DOSSIER_SPECIAL_DATE",
            "filterValue": "2023-12-30",
            "operator": "BEFORE"
        }
    ]
}

RELATIVE DATE​

A party filter that filters for parties that were onboarded more than 2 years ago​
{
    "metadataFilters": [
        {
            "filterKey": "ONBOARDING_DATE",
            "filterValue": "< -2y",
            "operator": "RELATIVE_DATE"
        }
    ]
}

LIKE​

 {
        "metadataFilters": [
            {
                "filterKey": "initial_level_due_diligence",
                "filterValue": "TESTING",
                "operator": "LIKE"
            }
        ]
}