Views:

Filtering is available as an optional parameter on numerous LUSID API endpoints.

Please visit our API documentation and/or Swagger specification (two views of the same material) to understand what filtering functionality is supported on specific APIs. 

Filtering entity fields

The filtering functionality allows you to specify a string defining the subset of results that are returned by the API call. Filters are specified using the LUSID filter syntax: 

<field> <operator> <value>


For example:

displayname eq 'MyPortfolio'

Filtering in LUSID is:

  • Case-insensitive
  • Does not return null values or values that are not set (except when filtering using not exists)

Operators

Supported operators are as follows: 

OperatorDescriptionExamples of valid expressions
eq

equals

Returns a record if the value exactly matches the specified value.

displayname eq 'MyPortfolio'

isDerived eq True

neq

not equals

Returns a record if the value doesn't match the specified value.

displayname neq 'MyPortfolio'

isDerived neq True

gt

greater than

Returns a record if the value is greater than the specified value.

created gt 2019-01-01

created gt 2019-01-15T10:00:00

unit gt 10

gte

greater than or equal to

Returns a record if the value is greater than or equal to the specified value.

created gte 2019-01-15

created gte 2017-02-15T18:00:00

unit gte 10

lt

less than

Returns a record if the value is less than the specified value.

created lt 2017-02-01

created lt 2017-02-15T18:00:00

unit lt 10

startswith

Returns a record if the value starts with the specified value.

displayName startswith ‘Mypo’

in

Returns a record if the value matches any of the specified values.

displayName in 'MyPortfolio', 'MyOtherPortfolio'

not in

Returns a record if the value is not in any of the specified values.

displayName not in 'My Portfolio', 'MyOtherPortfolio'

exists

Returns a record if the value is set.

properties.Portfolio/scope/code exists

not exists

Returns a record if the value is not set.

properties.Portfolio/scope/code not exists

and

Logical AND operator that connects two or more filter statements and returns a record if all resolve to true.

isDerived eq True and created lt 2017-03-15

or

Logical OR operator that connects two or more filter statements and returns a record if one resolves to true.

isDerived eq True or displayName eq 'MyPortfolio'

 

Note you can combine the and and or operators; and is evaluated before or following standard Boolean operator precedence. If you want to change the precedence, use parentheses. (So the order of evaluation is: parentheses, and, or.)

displayName eq 'MyPortfolio' or (isDerived eq True and created lt 2017-03-15)

not

The filter string can be preceded by a not operator that negates the results.

not displayName in 'MyPortfolio', 'MyOtherPortfolio'

Data types

The operators you can use depend on the data type of the field you want to filter on:

Data type of fieldExample fieldSupported operators
String

Scope, Code

eq, neq, startswith, in, not

DateTime

effectiveAt, asAt

eq, neq, gt, gte, lt, lte

DateOrCutLabel

Not supportedNot supported
Enum

quoteType

instrumendIdType

in, not in, eq, neq
BooleanisDerivedeq, neq

Dictionary 

properties/identifiersDepending on type of value

Array / List

Not supportedNot supported

Values

Values are case-insensitive and wildcard characters are not supported.

Filtering entity identifiers

Some LUSID entities such as instruments support filtering on identifiers. Instrument identifiers can be filtered by providing the identifier key for the identifiers collection, for example:

identifiers['Currency'] eq 'CCY_USD'


This filter returns all records where the identifier type Currency is set to CCY_USD.

Filtering custom entity fields

If you create your own custom entities, you can filter on data field values using the fields collection, for example:

fields['postalCode'] startswith 'EC4'


This filter returns all the custom entities of a particular type located in the EC4 postal code area.

Filtering properties

Some LUSID API endpoints support decorating properties onto entities. The syntax to filter properties can be either:

properties[<key>] <operator> <value>

or:

properties.<key> <operator> <value>

The property key is a three-part identifier used to uniquely identify properties. The structure is <domain>/<scope>/<code>. The key is case sensitive. For example:

properties[Instrument/FinbourneScope/portfolioManager] eq 'Tom'
properties.Instrument/FinbourneScope/portfolioManager eq 'Tom'
properties[Instrument/FinbourneScope/startDate] eq 2019-01-01


For multi-value properties, the filter syntax reflects the fact you may want to match any or all of a property's values. For example:

  • properties[mydomain/myscope/mycode] any (~ startswith 're') matches both ["red", "green", "blue"] and ["red", "reed", "read"]
  • properties[mydomain/myscope/mycode] all (~ startswith 're') matches just ["red", "reed", "read"]

Note: For multi-value properties, using the eq operator in the standard way (for example properties[mydomain/myscope/mycode] eq 'red') acts as a shortcut for properties[mydomain/myscope/mycode] any (~ eq 'red').

Filtering sub-holding keys (SHKs)

You can filter on sub-holding keys (special transaction properties registered with a portfolio for the purpose of segmenting holdings into groups) using the SubHoldingKeys collection, for example:

SubHoldingKeys[Transaction/Demo/Strategy] eq 'Income'

Filtering nested fields

The LUSID API supports nested filtering. Fields not present at the root level can be accessed using the '.' syntax. Consider the following response schema for the ListPortfolio API endpoint below:

For example, to filter on a portfolio's scope:

id.scope eq 'Finbourne-Examples'

Limitations

Note the following:

  • Multi-value properties and derived properties are currently not filterable in LUSID 
  • Filtering on DateTimeCutLabel is not supported
  • Filtering on data types such as Arrays and Lists (denoted by [...]) is not supported
  • Filtering is still case sensitive for instrument identifiers, instrument properties and portfolio properties on List* API endpoints.

Error reporting

The LUSID API returns user-friendly messages that can be used to determine the cause of any errors. The table below summarises the error messages, including descriptions and potential resolutions:

Error Name CodeError DescriptionPotential Resolution
DataFilterApplicationFailure181There was a problem with the syntax of the provided filter. The problematic token was: 'equal'

Check the syntax supplied in the string is correct one. Look at the examples above.

In this case the operator 'equal' is not supported, the correct operator is eq.

DataFilterApplicationFailure181One or more failures occurred. Cannot apply 'operator' to a 'instrument' and a 'string'Operator used in the filter syntax is not supported. Please use the operators as described in the operator section above.
DataFilterApplicationFailure181One or more failures occurred. The field 'fieldName' referred to in the supplied filter is not valid or is not available for filteringField being used in the filtering is not currently supported. Check if the fieldName used is the correct one.
FilterExecutionTimeout415Timed out when attempting to filter records.LUSID allows a maximum time of 30 seconds for any filter query to run. Try improving the filter query by adding additional parameters or please send us an email on support@lusid.com to discuss your use case.
PropertyNotDefined121Property not definedMake sure that the key used for property filtering is the right one and uses the property filtering syntax as described above. Please note that the property key is case sensitive.

Searching fields

The search parameter is supported only on Search* API endpoints and can be used to make wildcard searches across all fields for a given resource. It applies across the whole record and utilizes features such as ElasticSearch tokenisation. Results returned from search can be filtered further to return only the relevant records.

For example, the following matches any records where the text string starts with Global:

Global*

This will match any records where Global is mentioned as a word token in the string. Tokens for Search API endpoints are delimited by looking at the space and dash literals in a given string:

Global