How to use query with nested struct function | Community
Skip to main content
A
akanshamishra1
Level 2
December 21, 2023
Solved

How to use query with nested struct function

  • December 21, 2023
  • 2 replies
  • 1226 views

I am facing error with this query while trying to use nested struct functions

ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Errors(Some(INGEST-1205-400),Some(List()),Some(The field: _XYZ does not exist in the target schema. Remove the field and try again.))]]

 

INSERT INTO recently_viewed_products
SELECT STRUCT(THIRD_ID,struct(struct(struct(PROD_ID as PROD_ID)PRODUCTDETAIL)PRODUCTS)COMMERCE)_XYZ,
PAGE_VIEWS_VALUE,
LINK_CLICKS_VALUE,
timestamp,
POIEXITS_VALUE,
POIENTRIES_VALUE,
_id
FROM (
SELECT *
FROM (
SELECT _XYZ.THIRD_ID THIRD_ID,
_XYZ.COMMERCE.PRODUCTS.PRODUCTDETAIL.PROD_ID PROD_ID,
web.webPageDetails.pageViews.value PAGE_VIEWS_VALUE,
web.webInteraction.linkClicks.value LINK_CLICKS_VALUE,
timestamp,
placeContext.POIinteraction.poiExits.value POIEXITS_VALUE,
placeContext.POIinteraction.poiEntries.value POIENTRIES_VALUE,
_id,
ROW_NUMBER() OVER (PARTITION BY _XYZ.THIRD_ID ORDER BY TIMESTAMP DESC) RW_NUM
FROM web_data
) AS subquery
WHERE RW_NUM < 6
);

    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 abhinavbalooni

    Hey @akanshamishra1 

     

    What does your schema look like ? The error mentions that fields you are trying to access aren't available. First step would be to check what the schema structure looks like and also what is the purpose of the above query ? There can be multiple possibilities for a struct to fail.

     

    Cheers,

    Abhinav

    2 replies

    abhinavbalooni
    Community Advisor
    abhinavbalooniCommunity AdvisorAccepted solution
    Community Advisor
    December 21, 2023

    Hey @akanshamishra1 

     

    What does your schema look like ? The error mentions that fields you are trying to access aren't available. First step would be to check what the schema structure looks like and also what is the purpose of the above query ? There can be multiple possibilities for a struct to fail.

     

    Cheers,

    Abhinav

      A
      akanshamishra1Author
      Level 2
      December 21, 2023

      Hi @abhinavbalooni 
      we are looking for _XYZ.COMMERCE.PRODUCTS.PRODUCTDETAIL.PROD_ID, _XYZ_THIRD_ID from the schema and want to insert this data field in the dataset.
      Schema is for web events data

        NimashaJain
        Adobe Employee
        NimashaJainAdobe Employee
        Adobe Employee
        January 16, 2024

        Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.

        Terms & ConditionsAccessibility statement

        Loading footer...