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_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 "entities" individual,
lateral flatten(input => individual."crosswalks") crosswalk
); SELECT ...
FROM "entities"
WHERE "type" = "configuration/entityTypes/Individual"