How to write Insert into query for nested objects dataset

[vkt1989]

I need to work on a query that will fetch results of join between two temp datasets that are not profile enabled then use that resultset to insert into profile enabled dataset. There is minimal mention in the documentation which doesn't show nested objects. 

E.g. Query I tried below did not work

Path of fields in Schema structure is like the below

_Tenant.ProfileAttributes.address.city
_Tenant.ProfileAttributes.role
_Tenant.Identity.customerID

The query that I tried is as below

INSERT INTO final_dataset
select struct
(
city as city,
role as role,
customerID as customerID
) _Tenant
From
(
select a._Tenant.ProfileAttributes.address.city as city
b._Tenant.ProfileAttributes.role as role
b._Tenant.Identity.customerID as customerID
from address a, profileDetails b
where a._Tenant.Identity.customerID = b._Tenant.Identity.customerID
)

any idea how to achieve the successful insertion.

[ChetanyaJain]

Hi @vkt1989,

What is the error that you are seeing?

Based on your query (select struct), its creating as _Tenant.city, _Tenant.role, _Tenant.customerID and this is not matching with your Target schema - and probably the reason for error.

Did you try in this way?

select struct
(
city as "ProfileAttributes.address.city",
role as "ProfileAttributes.role",
customerID as "Identity.customerID"
) _Tenant

Try and let me know if that works fine?

Regards,

Chetanya 

[vkt1989]

I tried this already but it did not work. Receiving the error in QS " [Errors(Some(INGEST-1207-400),Some(List()),Some(A required field _Tenant.Identity of type: object is missing from the input data. Add the required field and try again.))]"

At the dataset level. I see the below errors.

A required field _Tenant.Identity of type: object is missing from the input data. Add the required field and try again.

The field: _Tenant.ProfileAttributes.address.city does not exist in the target schema. Remove the field and try again.

The field: _Tenant.ProfileAttributes.role does not exist in the target schema. Remove the field and try again.

The field: _Tenant.Identity.customerID does not exist in the target schema. Remove the field and try again.

When we insert the data at root level attributes, it works but when we try to ingest with nested objects it doesn't work. Adobe's documentation doesn't have information on how to work with nested objects. SQL doesn't support dot(.) in columns so we having an issue here.

[Danny-Miller]

@vkt1989 What about something like this:

select struct( 'NY' as city) "_Tenant.ProfileAttributes.address",
struct('VP' as role) "_Tenant.ProfileAttributes",
struct('123' as customerID) "_Tenant"

OR

select struct( 'NY' as city) "_Tenant.ProfileAttributes.address.city",
struct('VP' as role) "_Tenant.ProfileAttributes.role",
struct('123' as customerID) "_Tenant.customerID"

[vkt1989]

Not working as dot(.) not allowed while defining struct ( "_Tenant.customerID").

Error : Column names can only contain alphanumeric or underscore characters]]]

[Anuhya-Y]

Hi @vkt1989 ,

Try this.

SELECT struct(struct(struct(city as city)address, role as role)ProfileAttributes, struct(customerID as customerID)Identity)_Tenant

Thanks,

Anu

[vkt1989]

Not sure why but always get a job aborted error when I try nested struct.

ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Failed to get results of query Reason: [Job aborted.]]]

[Anuhya-Y · Accepted solution]

Hi @vkt1989 ,

I tried to run below query successfully without any issue in our environment. please change field/object names according to your need and try.
58000 is related to "Internal system failure" , I believe it not issue with struct query construction.

_tenant.contactDetails.homePhone.type
_tenant.contactDetails.personalEmail
_tenant.custIdentities.epsId

SELECT struct(struct(struct(type as type)homePhone,personalEmail as personalEmail)contactDetails,struct(epsId as epsId)custIdentities)_tenant

from (select _tenant.custIdentities.epsId ,_tenant.contactDetails.homePhone.type as type,_tenant.contactDetails.personalEmail as personalEmail

from sample_ds)

Thanks,

Anu

[vkt1989]

Thanks @Anuhya-Y. The structure worked.

[vkt1989]

I am partially able to update objects that are 1 level below the tenant but still 2nd level nested objects unable to update as well as individual fields under the objects. Here is the working query for objects update except for nested 'address' object

INSERT INTO final_dataset

select struct (

ProfileAttributes as ProfileAttributes,

Identity as Identity ) _Tenant

From (

select b._Tenant.ProfileAttributes as ProfileAttributes,

a._Tenant.Identity as Identity

from address a, profileDetails b

where a._Tenant.Identity.customerID = b._Tenant.Identity.customerID )