Flattening Simple Attributes

Learn about extracting and flattening all simple attributes into a custom view.

Assume that you have the following schema for individual entity view:

Table 1. Schema for individal entity view
Column Type
uri VARCHAR(20)
type VARIANT
attributes VARIANT
crosswalks VARIANT
analyticsAttributes VARIANT
createdBy VARIANT
createdTime VARIANT
updatedBy VARIANT
updatedTime VARIANT

You can extract and flatten all simple attributes into a custom view. The following example creates a view where all simple attributes are extracted to different columns and any multi-valued simple attributes are flattened and created as separate rows:

//This is a view for simple attributes
            
 create or replace view "entity_Individual_SimpleAttributes" 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",
 individual."attributes":"Name" as "FullName",                                                             
 individual."attributes":"FirstName" as "FirstName",
 individual."attributes":"LastName" as "LastName",
 individual."attributes":"Gender" as "Gender",
 individual."attributes":"Age" as "Age",
 individual."attributes":"Ethnicity" as "Ethnicity",
 individual."attributes":"BirthCountry" as "BirthCountry",     
 individual."attributes":"Status" as "Status"                                                                
 from "entity_Individual" individual                                                      
 );

Query: Get all individuals whose age is more than 60.

select "Uri","FirstName","LastName", "Age" 
from "RELTIODEMO_DB"."DEMOSCHEMA"."entity_Individual_SimpleAttributes" 
where TO_NUMBER("Age") > 60 
limit 10;