Expand my Community achievements bar.

Query Service-Does not return all columns


Level 3


I've a dataset which contains 51 columns but when I query my dataset in query service it return just 16 columns.

Column in dataset : 

_id endUserIDs._experience.emailid.id web.webInteraction.URL
 application.launches.value endUserIDs._experience.emailid.namespace.code web.webInteraction.linkClicks.id
channel._id endUserIDs._experience.mcid.id web.webInteraction.linkClicks.value
channel._type endUserIDs._experience.mcid.namespace.code web.webInteraction.name
channel.mediaAction environment.duration web.webInteraction.type
channel.mediaType eventType web.webPageDetails.URL
commerce.checkouts.value identityMap web.webPageDetails.isErrorPage
commerce.order.currencyCode marketing.campaignGroup web.webPageDetails.name
commerce.order.payments marketing.campaignName web.webPageDetails.pageViews.value
commerce.order.priceTotal marketing.trackingCode web.webReferrer.URL
commerce.order.purchaseID placeContext.geo._id web.webReferrer.type
commerce.order.purchaseOrderNumber placeContext.geo._schema.latitude dataSource._id
commerce.productListAdds.value placeContext.geo._schema.longitude dataSource.code
commerce.productListViews.value placeContext.geo.city device.type
commerce.productViews.value productListItems search.position
commerce.purchases.value search.isPaid search.searchEngine
commerce.saveForLaters.value search.keywords timestamp


Get List of Columns : 

  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = '<tablename>'

Query : 



Column return by query service/Query:

application web
eventType placeContext
channel identityMap
timestamp marketing
_id environment
productListItems device
commerce search
endUserIDs dataSource


I can see, query service ignores the column name having dot(.) or underscore(_). I checked with other SQL client like DbVisualizer and HeidiSQL. It returns 16 column only. Does it have any restriction at column naming convention?



Topics help categorize Community content and increase your ability to discover relevant content.

3 Replies


Level 3

Hi @anuhya-tad ,

Thanks for your reply. As per document, inline function supports array element. I have different data model which does not have arrays. Also, I feel inline function would be more helpful when we know the column name in advance. So I'm getting error as type mismatch.




Level 7

@vikash4 If fields are created directly under the root object of the schema (_tenantId), select * query will display all the columns. But when object/array type field is used in schema, nested columns of the object will not display with select * query.


AEP is supporting couple of functions which supports array type fields to unnest columns like explode, flatten, inline etc. check https://experienceleague.adobe.com/docs/experience-platform/query/sql/spark-sql-functions.html?lang=.... I have not come across function in AEP which can unnest/flatten object type filed.