Query Service-Does not return all columns | Community
Skip to main content
V
Vikashyadav
New Participant
February 17, 2023
Solved

Query Service-Does not return all columns

  • February 17, 2023
  • 1 reply
  • 2044 views

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.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Anuhya-Y

    @vikashyadav 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=en. I have not come across function in AEP which can unnest/flatten object type filed.

    1 reply

    Anuhya-Y
    Anuhya-Y
    New Participant
    February 17, 2023

    @vikashyadav 

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

      V
      VikashyadavAuthor
      New Participant
      February 17, 2023

      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
      Anuhya-YAccepted solution
      New Participant
      February 20, 2023

      @vikashyadav 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=en. I have not come across function in AEP which can unnest/flatten object type filed.

        Terms & ConditionsAccessibility statement

        Loading footer...