Flattening Nested and Reference Attributes

Learn about extracting and flattening nested and reference attributes into a custom view.

Similar to simple attributes, nested, and reference attributes can be flattened and extracted to separate views for easy querying. Here's an example.

Suppose you have a reference attribute Addresses in entity Individual, you can flatten it out using the below query. In this view the Addresses attribute is flattened to separate columns like AddressLine1, AddressLine2, City, StateProvince, Country, and Zip5. If you have multiple addresses for a single individual, then it is flattened and extracted to separate rows.

//This is a view for nested attribute (addresses)
create or replace view "entity_Addresses" 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",
a.value:"AVC" as "AVC",
a.value:"AddressLine1" as "AddressLine1",
a.value:"AddressLine2" as "AddressLine2",
a.value:"City" as "City",
a.value:"StateProvince" as "StateProvince",
a.value:"Country" as "Country",
a.value:"Zip5" as "Zip5"
from "entities" individual,
lateral flatten(input => individual."attributes":"Addresses") a
);

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

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

Footer

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 Nested and Reference Attributes

Footer