Filtering entities based on attributes of related entities

You can filter entities in LUSID based on attributes of related entities.

Note: To get started with filtering, including information on supported APIs and operators, and how to filter entities based on their own attributes, see this article. For filtering performance, see this article.

You can create relationships between certain types of entity in LUSID. For example, imagine we have an instrument representing BP shares that has a relationship with a legal entity representing the issuer, and a separate relationship with a custom entity representing a data quality check:

You can filter instruments on relationship data and/or on related entity data using the syntax  relationships[<type>] <match> (<filter>), where:

  • <type> optionally restricts the relationship to a particular type using the scope and code of the relationship type, for example relationships[Instruments/IssuerId]. Note that relationships[Instruments/IssuerId:out] can be used to restrict to a type and a traversal direction.

  • <match> is any to retrieve entities with partially matching criteria or all to retrieve entities with exactly matching criteria.

  • <filter> is any other filter expression(s) documented in this article.

Note the following:

  • To be clear, you are retrieving not the relationships or related entities themselves but rather (in this example) instruments with matching relationship or related entity criteria.

  • Not all entity types currently support relationships.

Relationships are bi-directional and you can filter from either side. So for example you can:

  • Retrieve instruments by filtering legal entities. For example, to retrieve all the instruments issued by a particular organisation: 
    relationships any (LegalEntity.identifiers[LegalEntity/Issuers/LEI] eq '546789VABC')

  • Retrieve legal entities by filtering instruments. For example, to retrieve all the legal entities responsible for issuing a particular ISIN: 
    relationships any (Instrument.identifiers[Instrument/default/Isin] eq 'GB0007980591')

See the examples below for more information. Note you must qualify identifiers, fields and properties of related entities with the entity type, so for example in the following filter...

relationships any (relationshipDefinitionId.scope eq 'InstrumentIssuer' and LegalEntity.Properties[LegalEntity/Scores/DEI] gt 40)

  • ...the relationshipDefinitionId.scope eq 'InstrumentIssuer' clause filters on relationship data.

  • ...the LegalEntity.Properties[LegalEntity/Scores/DEI] gt 40 clause filters on related entity data. Note the LegalEntity. prefix distinguishes attributes belonging to related legal entities from attributes belonging to the relationships themselves.

To retrieve instruments...

Apply the filter...

...where any related legal entity has a similar identifier

relationships any (LegalEntity.identifiers[LegalEntity/Issuers/LEI] startswith '213')

...where any related legal entity has a particular display name and description

relationships any (LegalEntity.fields[displayName] eq 'Issuer' and LegalEntity.fields[description] eq 'Legal responsibility')

...where any legal entity related by a particular relationship type has a similar identifier

relationships[InstrumentIssuer/IssuerId] any (LegalEntity.identifiers[LegalEntity/Issuers/LEI] startswith '213')

...that have relationships with a particular description

relationships any (traversalDescription eq 'IssuedBy')

...that have relationships

relationships any

...that have no relationships

relationships none

To retrieve instruments where any related legal entity has a DEIScore property...

Assumes DEIScore is a...

Apply the filter...

...where the property value is greater than 40

Single-value perpetual property

relationships any (LegalEntity.properties[LegalEntity/Scores/DEIScore] gt 40)

...where any property value is greater than 40

Multi-value perpetual property

relationships any (LegalEntity.properties[LegalEntity/Scores/DEIScore] any (~ gt 40))

...where all property values are greater than 40

Multi-value perpetual property

relationships any (LegalEntity.properties[LegalEntity/Scores/DEIScore] all (~ gt 40))

To retrieve instruments where all related legal entities have a DEIScore property...

Assumes DEIScore is a...

Apply the filter...

...where the property value is greater than 40

Single-value perpetual property

relationships all (LegalEntity.properties[LegalEntity/Scores/DEIScore] gt 40)

...where any property value is greater than 40

Multi-value perpetual property

relationships all (LegalEntity.properties[LegalEntity/Scores/DEIScore] any (~ gt 40))

...where all property values are greater than 40

Multi-value perpetual property

relationships all (LegalEntity.properties[LegalEntity/Scores/DEIScore] all (~ gt 40))