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
andall
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> )
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> )
all Contacts have ( isInfluencer = '0' )
It is possible to cascade through several relationships' roles using an any
or all
condition.
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.
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>
. -
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
anddistinct_string_agg
use theseparated by <separator>
clause. -
The
order by
clause is supported indistinct_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.
-
-
-
<order_by_clause_on_child_entity>
: serves as a sorting condition for expressions, used in thefirst
andstring_agg
lookup types.
lookup sum Value from Orders where Status = 'Paid' end
lookup first Email from Contacts where Primary = 1 order by CreationDate desc end
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
.
GoldenRecord
pseudo-role and its number of master records (MastersCount
)GoldenRecord.MastersCount > 1
CustomerName
consolidated into the golden record that results from a given source recordMasterRecord.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.
MastersCount > 1 and all MasterRecords have ( PublisherID = 'CRM' )
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. |