Flattening Crosswalks

Learn about extracting and flattening crosswalks into a custom view.

Crosswalks can be flattened to a separate custom view and queried similar to other views. You can use the SQL statement given below to create a simple view for crosswalks where each crosswalk is flattened and multiple crosswalk sources are extracted to separate rows.

//This is a view for crosswalks
create or replace view "entity_Individual_Crosswalks" 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",
crosswalk.value:"createDate" as "CrosswalkCreatedDate",
crosswalk.value:"reltioLoadDate" as "CrosswalkLoadDate",  
crosswalk.value:"updateDate" as "CrosswalkUpdateDate",   
crosswalk.value:"type" as "CrosswalkSource",  
crosswalk.value:"uri" as "CrosswalkUri",  
crosswalk.value:"value" as "CrosswalkValue"   
from "entity_Individual" individual,
lateral flatten(input => individual."crosswalks") crosswalk

Query: Find entities which have more than four crosswalk sources.

select a."Uri", count(*) as crosswalk_count 
from "entity_Individual_Crosswalks" a group by a."Uri" having crosswalk_count > 4;
limit 10;

Query: Find all entities with source system as configuration/sources/website.

select distinct "Uri" from "entity_Individual_Crosswalks"
where "CrosswalkSource" like '%configuration/sources/Website%';
limit 10;