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.

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"

Accelerate the Value of Data

Flattening Nested and Reference Attributes

Footer