Views:

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

Please visit our API and Swagger documentation to understand what filtering functionality is supported on specific APIs. 

Filtering on 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 , which has the following format: <field> operator <value>. Filtering in LUSID is

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

Operator

This is the logical operator that determine how the specified value will be compared against records in LUSID. The table below outlines the operators supported by LUSID. 

Operator Description Examples 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*

starts with

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 / or logical operators that can be used to compound two or more queries

isDerived eq True and created lt 2017-03-15

isDerived eq True or displayName eq 'MyPortfolio'

not Filter string can be preceded with a not operator that will negate the results from the filter string  not displayName in 'MyPortfolio', 'MyOtherPOrtfolio'

 

Field

This is an attribute of the schema returned in the response of a given API endpoint.

The filtering functionality supported varies based on the field's data type. The table below summarises the type of operators supported for different data types in LUSID.

Data Type Example field Supported operators
String

Scope, Code

eq, ne, startswith*, in, not

DateTime

effectiveAt, asAt

eq, ne, gt, gte, lt, lte

DateOrCutLabel (Not supported)

effectiveAt, asAt

NA
Enum

quoteType

instrumendIdType

in, not in, eq, ne
Boolean isDerived eq, ne

Dictionary (Not supported)  

identifiers Depending on type of value

Array / List (Not supported)

NA NA

 

Value

This is a user defined value that the user wishes to filter on. For ease of discovering information, LUSID supports case-insensitive filtering. Wildcard characters are not supported here.

Property filtering

Some LUSID API end points support decorating additional metadata information with properties. Properties can be filtered in the same way by providing a three part property key and one of the above operators.
  • properties[key] <operator> value
  • properties.key <operator> value

Key

This is a three part identifier that is used to identify properties. Structure of this is [Domain]/[Scope]/[Code]. Please note that the property key supplied here is case sensitive.

Examples of valid expressions

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

Filtering on 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 ListPortfolio API below:

To filter on scope for the portfolio the syntax used will be 

  • id.scope eq 'Finbourne-Examples'

Limitations

  • 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. 
  • *startswith filtering is not optimized for performance

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  Code Error Description Potential Resolution
DataFilterApplicationFailure 181 There 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

DataFilterApplicationFailure 181 One 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
DataFilterApplicationFailure 181 One or more failures occurred. The field 'fieldName' referred to in the supplied filter is not valid or is not available for filtering Field being used in the filtering is not currently supported. Check if the fieldName used is the correct one 
FilterExecutionTimeout 415 Timed 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
PropertyNotDefined 121 Property not defined Make 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

Example:

Global*

This will match 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