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