Query service in AEP error for adobe analytics data

[gautham_madala]

Hello, 
I have an error in query service within AEP when I use set auto_to_json=true; 

and them run the select * from adobe analytics data; 
Error:  Cannot read properties of undefined (reading 'map')
I have just use the dataset that is created from adobe source connector. 
I have 3k+ fields within this that is in JSON format. 
How does one query this within the query service to get all columns auto populated. ?

[supratim320]

You have to remove "Set auto_to_json=true;"

This function tries to convert every data into json/json string type of object and you must be having some "Map" (identityMap?) field in the schema which it is unable to convert.

It works when the data is in below format:

Before data: 

[AAID -> "{(31892EE080007B35-E6CE00000000000,t)}"]

After data: 

{"AAID":[{"id":"31892EE080007B35-E6CE00000000000","primary":true}]}

  So you can check the data of that Map type field, I think it does not look identical to "before data", hence failing

[gautham_madala]

Thats right. 
What do you suggest me to do in this case ? 

[supratim320]

1. If you do not need the "Map" field(s), then discard the field(s) from sql and go ahead with "set auto_to_json=true"
2. If you need the "Map" type in sql, then get rid of "set auto_to_json=true" and only use "to_json(<columnName>)" - sample: 

SELECT 
  to_json(identitymap), to_json(endUserIds) 
FROM <dataset> 
ORDER BY TIMESTAMP DESC
LIMIT 5​

Hope this resolves!

[PradeepJa3]

but what if we dont know in advance which column has map fields ?

is there another query we can use to get type of each field ? I tried this query but it shows root level object as text dataType, and doesnt drill down to each fields within the object.

SELECT column_name, data_type
FROM
information_schema.columns
WHERE
table_name = 'xx'

Or is there a query we can use to automatically exclude any column of map type from the query ?

[supratim320]

You can look into catalog API where you will get the full view: url

Use an extra query param:

• Key = properties
• Value = observableSchema

[kautuk_sahni]

@gautham_madala Just checking in — were you able to resolve your issue?
We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could really benefit the community. Thanks again for being part of the conversation!

[gautham_madala]

Well I was not able to solve it. But I have left that issue to backlog and revisit sometime later. 

[supratim320]

Hi @gautham_madala,

Normally I use python to connect with query service or I have seen some using Dbvisualizer

Could you please give it a try here instead of the query window inside AEP. It worked for me.

Hope this resolves!

[gautham_madala]

Interesting. 
Could you post a screenshot, Ill check 
Thanks

[supratim320]

Hopefully this is what you are looking for!