Use attributes of related entities

A given SemQL clause can access attributes of entities related to the entity for which the SemQL clause is expressed. These related entities may be parent entities (where the current entity has zero or one parent) or child entities (where the current entity has zero or more children) within a given relationship.

Parent entities

It is possible to access attributes of a parent entity by prefixing an attribute with the role of the parent in the relationship.

The current Customer entity references the Employee entity in a relationship. In this relationship, the role of the second entity is AccountManager. The AccountManager.FirstName attribute refers to the FirstName of the Employee that is the parent—​in this relationship, the AccountManager—​of the current Customer.

Referring to parent entities can involve navigating up a chain of relationships.

AccountManager.CostCenter.CostCenterName navigates two relationships to return the CostCenterName of the CostCenter to which the AcountManager of the current Customer reports.

Child entities

You may access, from an entity, information in its child entities with two types of expressions:

  • The any and all conditions allow filtering using data from child entities. This syntax works exclusively in conditions.

  • The lookup expression allows looking up and aggregating information from child entities.

Any and all conditions

The any condition returns true if one of the child records meets the given condition.

any <child_entity_role> have ( <condition_on_child_entity> )

or

any <child_entity_role> has ( <condition_on_child_entity> )
Filter customers having at least one contact named John
any Contacts have ( FirstName = 'John' )

The all condition returns true if all the child records meet the given condition.

all <child_entity_role> have ( <condition_on_child_entity> )

or

all <child_entity_role> has ( <condition_on_child_entity> )
Filter customers having no contact identified as an influencer
all Contacts have ( isInfluencer = '0' )

It is possible to cascade through several relationships' roles using an any or all condition.

Filter employees managing customers with one contact identified as an influencer
any Customers.Contacts have ( IsInfluencer = '1' )

In any and all conditions, it is possible to access the direct parent’s record from the condition on the child entity, through the ParentRecord reserved keyword or through the parent’s role name in the relationship.

Filter all customers having two contacts with a different ContactID, but the same FirstName
any Contacts have (
    any ParentRecord.Contacts have (
        ParentRecord.ContactID != ContactID and ParentRecord.FirstName = FirstName
    )
)

Lookup expression

The lookup expression returns values or aggregated values from a child entity.

lookup
    <lookup_type> <expression_on_child_entity>
    separated by <separator>
    from <child_entity_role>
    where <condition_on_child_entity>
    order by <order_by_clause_on_child_entity>
end

The components of the lookup syntax are:

  • <child_entity_role>: indicates the path to a child entity via relationships. It can be a direct path through the child entity role (e.g., Contacts) or a nested path (e.g., Contacts.Emails).

  • <condition_on_child_entity>: functions a is a filtering condition, ensuring that only child records matching this condition are considered for the lookup.

  • <expression_on_child_entity>: represents a SemQL expression to return. This expression is resolved for the child record.

  • <lookup_type>: specifies the expression or aggregation returned from child records. Options include:

    • average: average of values.

    • count: count of values.

    • distinct_count: distinct count of values.

    • distinct_string_agg: concatenation of distinct values, ordered by <order_by_clause_on_child_entity> and separated by <separator>.

    • first: value in the first record. Records are ordered according to <order_by_clause_on_child_entity>.

    • limit: maximum number of records to return, specified as a positive integer.

    • max: largest value.

    • min: smallest value.

    • string_agg: concatenation of values, ordered by <order_by_clause_on_child_entity> and separated by <separator>.

    • sum: sum of values.

      Considerations regarding lookup types

      • Only string_agg and distinct_string_agg use the separated by <separator> clause.

      • The order by clause is supported in distinct_string_agg only for PostgreSQL and Oracle 19c+.

      • The <separator> value may be a literal or an expression. Expressions can only use attributes from the parent entity context of the lookup; attributes from the child entity are not permissible.

      • Expression separators are not supported in SQL Server.

      • The order by clause is necessary when using a limit clause. It should cover all columns used in a limit clause to ensure consistent results across subsequent calls.

      • The first clause does not accept the limit keyword since lookup first is essentially a special case of limit 1.

  • <order_by_clause_on_child_entity>: serves as a sorting condition for expressions, used in the first and string_agg lookup types.

Get the sum of values for orders that are marked as paid
lookup
    sum Value
    from Orders
    where Status = 'Paid'
end
Lookup for the email of the most recent contact that is marked as primary
lookup
    first Email
    from Contacts
    where Primary = 1
    order by CreationDate desc
end
Aggregate error details
lookup
    string_agg ErrorType || '-' || ErrorName
    separated by ','
    from Errors
    order by ErrorType, ErrorName
end

Using lineage

You can navigate records lineage using SemQL. Using this navigation, you can for example access the master and the golden records consolidated from a source record, or you can access all the attached master records from a golden record.

For more information on lineage structures, see The data certification process.

Lineage parent records

You can access the attributes of a parent record related to your current record in the lineage.

This navigation is possible using a pseudo-role name representing the parent relationship in the lineage, such as GoldenRecord (of a master record, for example), MasterRecord, or SourceRecord.

Filter out master records that are singletons by accessing their golden record using the GoldenRecord pseudo-role and its number of master records (MastersCount)
GoldenRecord.MastersCount > 1
Get CustomerName consolidated into the golden record that results from a given source record
MasterRecord.GoldenRecord.CustomerName
The complete list of built-in lineage navigation is available in Data access views, attributes, and lineage
The SemQL editor automatically lists the lineage navigation available depending on the situation.

Lineage child records

You can access the attributes of child records related to your current record in the lineage.

This navigation is possible using a pseudo-role name representing the child records in the lineage relationship, such as MasterRecords (the master records attached to a golden record) or SourceRecords (the source records attached to a master record).

You can use the lineage child records similarly to the Child entities records, using the SemQL any and all syntax.

Filter golden records that have duplicates only from the CRM publisher
MastersCount > 1
and
all MasterRecords have ( PublisherID = 'CRM' )
Filter all master records created from source records older than five days
all SourceRecords have (CreationDate < SYSDATE() - 5)

For the complete list of built-in lineage navigation, see Data access views, attributes, and lineage.

The SemQL editor automatically lists the lineage navigation available depending on the context.