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"

You've found your way to a temporary environment. Content is not complete or final. Looking for Reltio documentation? Go to docs.reltio.com.

© 2024 The RELTIO® mark is a registered trademark of Reltio, Inc. in the United States and other countries; DataDriven and Reltio Data Cloud are trademarks of Reltio, Inc. Other names or marks are trademarks of their respective owners.

Terms, Privacy Policy, and

Unify and manage your data

Flattening Simple Attributes

Footer