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_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 "entities" individual                                                      
 );

For example, to find all entities with the entity type 'Individual', use the following query:

SELECT ...
FROM "entities"
WHERE "type" = "configuration/entityTypes/Individual"

Accelerate the Value of Data

Flattening Simple Attributes

Footer