How to use query with nested struct function

[akanshamishra1]

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
);

[abhinavbalooni · Accepted solution]

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

[akanshamishra1]

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]

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.