Query Service-Does not return all columns

[vikash4]

Hi,

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 : 

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

Query : 

SELECT * FROM <TABLENAME>

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?

Thanks.

[Anuhya-Y]

@vikash4 

Try to use inline function to expand array object column . Check https://experienceleague.adobe.com/docs/experience-platform/query/sql/syntax.html

[vikash4]

Hi @Anuhya-Y ,

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.

Thanks.

[Anuhya-Y · Accepted solution]

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