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
- Property filtering
- Filtering on nested fields
- Error reporting
- Searching 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
- Does not return null values or values that are not set (Except when filtering using not exists)
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|
returns a record if the value exactly matches the specified value
displayname eq 'MyPortfolio'
isDerived eq True
returns a record if the value doesn't match the specified value
displayname neq 'MyPortfolio'
isDerived neq True
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
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
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
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'|
|not||Filter string can be preceded with a not operator that will negate the results from the filter string||not displayName in 'MyPortfolio', 'MyOtherPOrtfolio'|
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|
|eq, ne, startswith*, in, not|
|eq, ne, gt, gte, lt, lte|
DateOrCutLabel (Not supported)
|in, not in, eq, ne|
|properties/identifiers||Depending on type of value|
Array / List (Not supported)
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.
Some LUSID entities such as instruments support filtering on identifiers. Records can be filtered using instrument identifiers by providing the identifier key for the identifier collection. Syntax to filter records based on identifiers is as follows
- identifiers['Currency'] eq 'CCY_USD'
This will find all records where the identifierType Currency is set to CCY_USD
- properties[key] <operator> value
- properties.key <operator> value
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
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'
- 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
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 email@example.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|
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
This will match any records where the text string starts with 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