Filtering information retrieved from LUSID

Views:

You can retrieve data from LUSID using API endpoints designed to return one, some, or all instances of a particular entity type.

Note: This article describes filtering entities based on their own attributes. For information on filtering entities based on attributes of related entities, see this article. For filtering performance, see this article.

For example, to retrieve instruments from the LUSID security master, you might call:

The ListInstruments API to retrieve all instruments.
The GetInstruments API to retrieve multiple instruments with particular identifiers, for example FIGI BBG000FV67D8 and ISIN GB00BDR05C01.
The GetInstrument API to retrieve a single instrument with a particular identifier, for example FIGI BBG000FV67D8.

You can apply a filter to most API endpoints that retrieve more than one entity to restrict and qualify the data retrieved. For example, the following call to ListInstruments applies a filter expression of name startswith 'App' to retrieve just those instruments with 'App…'-like names:

curl -X GET "https://<your-domain>.lusid.com/api/api/instruments?filter=name%20startswith%20%27App%27" 
  -H "Authorization: Bearer <your-API-access-token>"

To see whether an API endpoint supports filtering, examine its LUSID API documentation and look for the filter query parameter:

Note that filtering in LUSID:

Is case-insensitive.
Must enclose string values in single straight quote marks (the %27 UTF-8 encoding).
Supports a set of operators such as eq and startswith; see Appendix A.
Does not return null values or values that are not set (except when filtering using the not exists operator).
Has some known limitations; see Appendix B.

Exploring filtering through an example dataset

Imagine we have three entities stored in LUSID: a legal entity representing BP PLC, an instrument representing BP shares, and a custom entity representing a data quality check:

Different types of entity have different attributes you can filter on:

Attribute	Entity type	Example	More information
Identifiers	Any except Person, Legal entity and Custom entity	`identifiers['Isin'] eq 'GB0007980591'`	Click here
Built-in data fields and nested fields	Any	`name eq 'BP' instrumentDefinition.instrumentType eq 'Equity'`	Click here
Custom data fields	Custom entity	`fields['lastCheck'] gt 2021-12-01`	Click here
Properties	Any	`properties[Instrument/Ibor/Industry] eq 'Energy'`	Click here
Version information/change history (not shown in picture)	Any	`version.asAtCreated gte 2023-06-01`	Click here
SHKs (not shown in picture)	Portfolio	`subholdingkeys[Transaction/Demo/Strategy] eq 'Income'`	Click here
Relationships (not shown in picture)	Instrument, Portfolio, Portfolio group, Person, Legal entity, Custom entity	See this article.

Filtering on identifiers

You can filter on an identifier using the syntax identifiers['<identifier>'] <operator> <value>, for example identifiers['Isin'] eq 'GB0007980591'.

Note: <identifier> is case sensitive, so identifiers['Isin'] is not the same as identifiers['ISIN']. Note the identifier value must be enclosed in single quote marks.

Filtering on built-in data fields, including nested fields

You can filter on a field using the syntax <field> <operator> <value>, for example name eq 'BP'.

You can filter on a nested field using dot notation, for example instrumentDefinition.instrumentType eq 'Equity'.

The names of fields you can filter on are available from Swagger in the response to a request to get an entity of that type. For example, the schema for a response to the GetInstrument API looks like this:

Note the following:

Filterable fields are underlined in red. For instruments in particular, this means the fields in the economic definition are NOT filterable; these fields are not available as nested fields within the InstrumentDefinition field.
The links and href fields are not filterable.
Identifiers, properties and relationships are not filterable as fields, but you can filter on them using syntax described elsewhere on this page.
For more information about filtering on the version field, see this section.
The JSON storage format of the field (string, enum, array, dictionary and so on) determines the filter operations you can perform; not all operators are available for every storage format.

Filtering custom entities on custom data fields

You can filter custom entities on a custom data field using the syntax fields['<field>'] <operator> <value>, for example fields['lastCheck'] gt 2021-12-01.

Note: The field value must be enclosed in single quote marks.

Filtering on properties

You can filter on a single-value property using the syntax properties[<key>] <operator> <value>, for example properties[Instrument/Ibor/Industry] eq 'Energy'.

Note: <key> is case sensitive, so properties[Instrument/Ibor/Industry] is not the same as properties[Instrument/IBOR/Industry]. The 3-stage key value should NOT be enclosed in single quote marks.

You can filter on a multi-value property using the syntax properties[<key>] <match> (~ <operator> <value>), where <match> is any to retrieve entities with partially matching values or all to retrieve just entities with exactly matching values. See the examples below for more information.

To retrieve instruments with an Industry property…	Assumes Industry is a…	Apply the filter expression…
...where the property value starts with ‘Energy’	Single-value perpetual or time-variant property	`properties[Instrument/Ibor/Industry] startsWith 'Energy'`
...where any property value starts with ‘Energy’	Multi-value perpetual or time-variant property	`properties[Instrument/Ibor/Industry] any (~ startsWith 'Energy')`
...where all property values start with ‘Energy’	Multi-value perpetual or time-variant property	`properties[Instrument/Ibor/Industry] all (~ startsWith 'Energy')`

Note: For multi-value properties, using the eq operator in the standard way (for example properties[Instrument/Ibor/Industry] eq 'Energy') acts as a shortcut for properties[Industry/Ibor/Industry] any (~ eq 'Energy').

Filtering entities based on version information/change history

You can filter most types of entity by version fields, prefixed by the version. keyword.

Note: Do not filter on the deprecated version.effectiveFrom and version.asAtDate fields.

Version field	Explanation	Example	Retrieves...
`asAtCreated`	The date on which the entity was created in LUSID.	`version.asAtCreated gte 2023-06-01`	All the entities created since 1 June 2023 inclusive.
`userIdCreated`	The ID of the user who created the entity.	`version.userIdCreated eq '00u7mi3egiUNgaCtW2p7'`	All the entities created by user A.
`requestIdCreated`	The ID of the request made by the creating user.	`version.requestIdCreated eq '0HMTEQI7E4N32:00000003'`	All the entities created in a particular (perhaps batch) request.
`asAtVersionNumber`	The number of times the entity has been modified. If the version number is 1, the entity has never been modified.	`version.asAtVersionNumber eq 1`	All the entities that have never been modified.
`asAtModified`	The date on which the entity was last modified. If the entity has not been modified, this is the same as `asAtCreated`.	`version.asAtModified gte 2023-06-01 and version.asAtModified lte 2023-09-01`	All the entities that were modified between two dates.
`userIdModified`	The ID of the user who last modified the entity. If the entity has not been modified, this is the same as `requestIdCreated` (and may in any case be the same user).	`version.userIdCreated eq '00u7mi3egiUNgaCtW2p7' and version.userIdModified eq '00ubs2temhwlYz2lI2p7'`	All the entities created by user A that have been modified by user B.
`requestIdModified`	The ID of the request made by the modifying user. If the entity has not been modified, this is the same as `requestIdCreated`.	`version.requestIdModified neq '0HMM1TL6UR57P:0000LH09'`	All the entities not modified in a particular request.

Filtering portfolios on subholding keys (SHKs)

You can filter portfolios on a sub-holding key using the syntax subholdingkeys[<key>] <operator> <value>, for example subholdingkeys[Transaction/Demo/Strategy] eq 'Income'. Note this is not shown in the example dataset above.

Note: <key> is case sensitive, so subholdingkeys[Transaction/Demo/Strategy] is not the same as subholdingkeys[Transaction/DEMO/Strategy]. The 3-stage key value should NOT be enclosed in single quote marks.

Appendix A: Supported operators

Note: For any operator in the table below, you can precede a filter expression with the not operator to reverse the operation.
For example, not displayName in 'MyPortfolio', 'MyOtherPortfolio'.

Operator	Meaning	Description	Filter expression examples
`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 does not 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-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-01 unit gte 10`
`lt`	Less than	Returns a record if the value is less than the specified value.	`created lt 2019-01-01 unit lt 10`
`lte`	Less than or equal to	Returns a record if the value is less than or equal to the specified value.	`created lte 2019-01-01 unit lte 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 'MyPortfolio', 'MyOtherPortfolio'`
`ct`	Contains token	Returns a record if the string value contains the specified token (that is, word). Note this is single token search only so you must chain expressions using `and`/`or` operators to search for multiple tokens (same applies to `ctsw` below).	For example, if an instrument has the name 'British Airways': `name ct 'airways'` returns it `name ct 'air'` does not return it `name ctsw 'air'` returns it `name ctsw 'rway'` does not return it
`ctsw`	Contains token starting with	Returns a record if the string value contains a word starting with the specified token.
`exists`		Returns a record if the value is set.	`properties[Portfolio/myscope/mycode] exists`
`not exists`		Returns a record if the value is not set.	`properties[Portfolio/myscope/mycode] not exists`
`and`	Logical AND operator	Connects two or more filter expressions and returns a record if all resolve to true.	`isDerived eq True and created lt 2017-03-15`
`or`	Logical OR operator	Connects two or more filter expressions 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)`
`any`		If used with a filter expression, returns a record if any of the values in a set match. If used without, returns a record if there are any values. For use with multi-value properties and relationships only.	`properties[Portfolio/myscope/mycode] any (~ eq 'Growth') properties[Portfolio/myscope/mycode] any`
`all`		Returns a record if all of the values in a set match a filter expression. For use with multi-value properties and relationships only.	`properties[Portfolio/myscope/mycode] all (~ eq 'Growth')`
`none`		Returns a record if there are no values in a set. For use with multi-value properties and relationships only.	`properties[Portfolio/myscope/mycode] none`

The operators you can use depend on the JSON storage format of the attribute you want to filter on:

Storage format	Example attributes	Supported operators
`string`	`displayName`, `scope`	`eq`, `neq`, `startswith`, `in`, `not`, `ct`, `ctsw`
`number`	`scaleFactor`	`eq`, `neq`, `gt`, `gte`, `lt`, `lte`
`date-time`	`effectiveFrom`	`eq`, `neq`, `gt`, `gte`, `lt`, `lte`
`boolean`	`isDerived`	`eq`, `neq`
`Enum`	`assetClass`, `quoteType`	`in`, `not in`, `eq`, `neq`
`dateorcutlabel`	Not supported	Not supported
Dictionary	properties, identifiers (depends on entity type)	Depends on storage format of values
Array/List	properties, identifiers (depends on entity type), relationships	Depends on storage format of values

Appendix B: Limitations and error reporting

Note the following:

Derived properties are currently not filterable in LUSID.
Filtering on attributes with a storage format of datetimecutlabel is not supported.
For multi-value properties, only values with 1024 characters or less are included in a filter result set in order to keep the system performant.

The LUSID API returns user-friendly messages that can be used to troubleshoot filtering errors:

Error name	Error code	Example description	Troubleshooting
`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. In this case the operator `equal` is not supported, the correct operator is `eq`.
		One or more failures occurred. Cannot apply 'operator' to a 'instrument' and a 'string'	Operator used in the filter syntax is not supported for the storage format. Please use the operators as described in Appendix A.
		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 `fieldName` is the correct name of a valid field in the schema.
`PropertyNotDefined`	`121`	Property not defined	Make sure that the 3-stage property key is correct and uses the property syntax described above. Please note that the property key is case sensitive.

Keywords: filtering, filter