Use Attributes of Related Entities

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

Parent Entities

It is possible to access attributes of a parent entity by prefixing this attribute by the Role of this parent in the relationship.

The current Customer entity references the Employee entity in a relationship. In this relationship, the Role of this 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 follow a chain of relationships.

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

Child Entities

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

The any/all conditions allows you to filter using data from child entities. This syntax works in conditions only.
The lookup expression allows you to lookup and aggregate information from child entities.

Any/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> )

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> )

all <child_entity_role> has ( <condition_on_child_entity> )

Filter Customers who have no Contact who is an influencer:

all Contacts have ( isInfluencer = '0' )

It is possible to cascade through several relations' roles using the any/all conditions.

Filter Employees managing Customers having one their Contacts who is an influencer:

any Customers.Contacts have ( IsInfluencer = '1' )

In the any/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.

All the 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>: This path points to a child entity via the relationships. It may be a direct path through the child entity role (e.g.: Contacts), or a path (e.g.: Contacts.Emails).
<expression_on_child_entity> is a SemQL expression to return. This expression is resolved for the child record.

<lookup_type> defines which expression or aggregation is returned from the child records:

min: The smallest value.
max: The largest value.
first: The value in the first record. Records are ordered according to the <order_by_clause_on_child_entity>.
sum: The sum of the values.
average: The average of the values.
count: The count of values.
distinct_count: The distinct count of values.
string_agg: A concatenation of the values, ordered by the <order_by_clause_on_child_entity>, and separated by the <separator>.

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

Note that:

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 may only use attributes in the context of the parent entity of the lookup. You cannot use in these expressions attributes from the child entity. Note that expression separators are not supported in SQL Server.

<condition_on_child_entity> is a filtering condition: only child records matching this condition are considered for the lookup.
<order_by_clause_on_child_entity> is a sorting condition for the expressions, used in the first and string_agg lookup types.

Get the sum of the values for the orders marked as paid.

lookup
    sum Value
    from Orders
    where Status = 'Paid'
end

Lookup for the email of the most recent Contact marked as primary

lookup
    first Email
    from Contacts
    where Primary = 1
    order by CreationDate desc
end

Aggregate Errors Details

lookup
    string_agg ErrorType || '-' || ErrorName
    separated by ','
    from Errors
    order by ErrorType, ErrorName
end

Using the 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.

To understand the lineage structures, refer to 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.

To filter out the master records that are singletons, you can access the golden record using the GoldenRecord pseudo-role and then use its number of masters MastersCount:

GoldenRecord.MastersCount > 1

To get the CustomerName consolidated in 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.

To filter golden records that have duplicates only from the CRM publisher:

MastersCount > 1
and
all MasterRecords have ( PublisherID = 'CRM' )

To filter all master records created from source records older than 5 days.

all SourceRecords have (CreationDate < SYSDATE() - 5)

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.