Flattening Nested and Reference Attributes

Learn about extracting and flattening nested and reference attributes into a custom view.

Similar to simple attributes, nested, and reference attributes can be flattened and extracted to separate views for easy querying.

Suppose you have a reference attribute Addresses in entity Individual, you can flatten it out using the below query. In this view the Addresses attribute is flattened to separate columns like AddressLine1, AddressLine2, City, StateProvince, Country, and Zip5. If you have multiple addresses for a single individual, then it is flattened and extracted to separate rows.

//This is a view for nested attribute (addresses)
create or replace view "entity_Individual_Addresses" as ( select individual."uri" as "Uri",
individual."type" as "Type",
individual."createdBy" as "CreatedBy",
individual."createdTime" as "CreatedTime",
individual."updatedBy" as "UpdatedBy",
individual."updatedTime" as "UpdatedTime",
a.value:"AVC" as "AVC",
a.value:"AddressLine1" as "AddressLine1",
a.value:"AddressLine2" as "AddressLine2",
a.value:"City" as "City",
a.value:"StateProvince" as "StateProvince",
a.value:"Country" as "Country",
a.value:"Zip5" as "Zip5"
from "entity_Individual" individual,
lateral flatten(input => individual."attributes":"Addresses") a
);

Query: Get all individuals who reside in US Chicago.

select "Uri","AddressLine1","City", "StateProvince", "Country", "Zip5" 
from "RELTIODEMO_DB"."DEMOSCHEMA"."entity_Individual_Addresses"
where "Country" like '%US%' and "City" like '%Chicago%' and "AddressLine1" is not null
limit 10

Similarly, custom views can be built for other nested attributes like Email and used in join queries for more advanced queries.

//This is a view for nested attribute (Email)
create or replace view "entity_Individual_Email" as ( select individual."uri" as "Uri",
individual."type" as "Type",
individual."createdBy" as "CreatedBy",
individual."createdTime" as "CreatedTime",
individual."updatedBy" as "UpdatedBy",
individual."updatedTime" as "UpdatedTime",
a.value:"Type" as "EmailType",
a.value:"Domain" as "Domain",  
a.value:"DomainType" as "DomainType",  
a.value:"Email" as "Email",  
a.value:"Username" as "Username"                              
from "entity_Individual" individual,
lateral flatten(input => individual."attributes":"Email") a
);

Query: Get all individuals' personal email addresses who reside in US Chicago.

select a."Uri", b."EmailType", b."Email" from "entity_Individual_Addresses" a join "entity_Individual_Email" b 
on a."Uri" = b."Uri" where a."Country" like '%US%' and a."City" like '%Chicago%' and a."AddressLine1" is not null
limit 10