Athena query to extract Array JSON data. Error:mismatched input '<EOF>'. Expecting: 'ON', 'USING'

Q&A
,
1

I have to extract below array JSON data from AWS Athena database

bouncedrecipients = 
[
  {
    "diagnosticcode": "smtp; 550 5.1.1 As requested: user unknown <bounce@simulator.amazonses.com>",
    "action": "failed",
    "emailaddress": "bounce@simulator.amazonses.com",
    "status": "5.1.1"
  }
]

I tried below query, which threw error:

Query:

WITH raw_data AS (
    SELECT 
        CASE 
            WHEN l.eventtype IS NULL THEN l.notificationtype 
            ELSE l.eventtype
        END AS event,
        l.mail.messageid,
        l.mail.timestamp AS mail_startdate,
        l.mail.source,
        l.mail.destination,
        l.delivery.processingtimemillis,
        l.delivery.smtpresponse AS delivery_response,
        l.delivery.timestamp AS delivery_timestamp,
        l.bounce.bouncetype,
        l.bounce.bouncesubtype,
        l.bounce.bouncedrecipients,
        l.bounce.timestamp AS bounced_timestamp,
        l.deliverydelay.delaytype,
        l.deliverydelay.delaysubtype,
        l.deliverydelay.delayedrecipients,
        l.deliverydelay.timestamp AS deliverydelay_timestamp,
        l.send.timestamp AS send_timestamp
    FROM ses_events.ses_all_events_logs l
)
SELECT 
    r.event,
    r.messageid,
    r.mail_startdate,
    r.source,
    r.destination,
    r.processingtimemillis,
    r.delivery_response,
    r.bouncetype,
    r.bouncesubtype,
    r.delaytype,
    r.delaysubtype,
    CASE 
        WHEN r.bouncedrecipients IS NOT NULL THEN json_extract_scalar(bouncedrecipients, '$.action')         
        WHEN r.delayedrecipients IS NOT NULL THEN json_extract_scalar(delayedrecipients, '$.action')
    END AS action,
    CASE 
        WHEN r.bouncedrecipients IS NOT NULL THEN json_extract_scalar(bouncedrecipients, '$.status')         
        WHEN r.delayedrecipients IS NOT NULL THEN json_extract_scalar(delayedrecipients, '$.status')
    END AS status,
    CASE 
        WHEN r.bouncedrecipients IS NOT NULL THEN json_extract_scalar(bouncedrecipients, '$.diagnosticcode')         
        WHEN r.delayedrecipients IS NOT NULL THEN json_extract_scalar(delayedrecipients, '$.diagnosticcode')
    END AS diagnosticcode,
    CASE 
        WHEN r.bouncedrecipients IS NOT NULL THEN json_extract_scalar(bouncedrecipients, '$.emailaddress')         
        WHEN r.delayedrecipients IS NOT NULL THEN json_extract_scalar(delayedrecipients, '$.emailaddress')
    END AS emailaddress,
    CASE 
        WHEN r.delivery_timestamp IS NOT NULL THEN r.delivery_timestamp      
        WHEN r.deliverydelay_timestamp IS NOT NULL THEN r.deliverydelay_timestamp  
        WHEN r.bounced_timestamp IS NOT NULL THEN r.bounced_timestamp  
        ELSE r.send_timestamp
    END AS enddatetime
FROM raw_data r
LEFT JOIN UNNEST(CAST(r.bouncedrecipients AS ARRAY(JSON))) AS t(bouncedrecipients)
LEFT JOIN UNNEST(CAST(r.delayedrecipients AS ARRAY(JSON))) AS d(delayedrecipients);

Error:

line 61:83: mismatched input '<EOF>'. Expecting: 'ON', 'USING'

Could you please help me with fixing the error.

Regards,

Pavitra

2

Hello Pavitra! Thanks for opening this issue.

When you use LEFT JOIN, you must specify how the tables should be joined using either ON or USING clause.

LEFT JOIN table2 ON table1.column = table2.column

Or

LEFT JOIN table2 USING (column_name)

However, in your specific case with UNNEST, you don’t actually need LEFT JOIN because UNNEST is expanding arrays, not joining tables. You can try:

FROM raw_data r,
     UNNEST(...) AS t(bouncedrecipients),
     UNNEST(...) AS d(delayedrecipients)

Let me know if it works!

3

Hi @Pavitra

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hi @Pavitra

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!