Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to write Insert into query for nested objects dataset

Avatar

Level 2

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.

1 Accepted Solution

Avatar

Correct answer by
Level 4

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

View solution in original post

3 Replies

Avatar

Level 5

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 

 

Avatar

Level 2

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.

 

Avatar

Employee

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

Avatar

Level 2

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

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

Avatar

Level 4

Hi @vkt1989 ,

Try this.

 

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

 

Thanks,

Anu

Avatar

Level 2

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.]]]

Avatar

Correct answer by
Level 4

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

Avatar

Level 2

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 )