How to write Insert into query for nested objects dataset | Community
Skip to main content
V
vkt1989
Level 2
September 9, 2022
Solved

How to write Insert into query for nested objects dataset

  • September 9, 2022
  • 3 replies
  • 3642 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Anuhya-Y

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

3 replies

ChetanyaJain-1
Community Advisor
ChetanyaJain-1Community Advisor
Community Advisor
September 11, 2022

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 

 

V
vkt1989Author
Level 2
September 12, 2022

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
Adobe Employee
Danny-MillerAdobe Employee
Adobe Employee
September 12, 2022

@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"

Anuhya-Y
Community Advisor
Anuhya-YCommunity Advisor
Community Advisor
September 13, 2022

Hi @vkt1989 ,

Try this.

 

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

 

Thanks,

Anu

V
vkt1989Author
Level 2
September 13, 2022

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
Community Advisor
Anuhya-YCommunity AdvisorAccepted solution
Community Advisor
September 13, 2022

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

    V
    vkt1989Author
    Level 2
    September 13, 2022

    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 )

      Terms & ConditionsAccessibility statement

      Loading footer...