asked on

MySQL: Need to extract email address only...

I have a single field in a log table that contains this data:

Is it JSON?

How do I extract just the email address following the "to" field?

I have a text file version and could use some other scripting tool, even Excel, if it could work.

Thanks.

{"event": "failed", "severity": "temporary", "tags": [], "mailing-list": {"list-id": "satb_mailing2_nonspam.m.satb.com", "address": "satb_mailing2@m.satb.com", "sid": "1288499"}, "timestamp": 1446076551.295771, "envelope": {"targets": "sb@reply.sb.com", "transport": "smtp", "sender": "postmaster@m.satb.com"}, "delivery-status": {"code": null, "message": null, "retry-seconds": 900, "description": "MX lookup failed", "session-seconds": 0.0}, "kafka": {"topic": "iad_pipeline_events", "msg_size": 1761, "partition": 1, "key": "NTVmMWU0OTg3OGZhMTY0ZmQzYWUxMDg5", "consumer_group": "logstash-customer"}, "campaigns": [{"id": "g7d2t", "name": "BapChOwn"}], "log-level": "warn", "reason": "generic", "user-variables": {}, "flags": {"is-routed": null, "is-authenticated": true, "is-system-test": false, "is-test-mode": false}, "recipient-domain": "reply.sb.com", "message": {"headers": {"to": "scottbrown@reply.scottbrown.com", "message-id": "20151028223554.10459.56766@m.satb.com", "from": "Roberta <rob@satb.com>", "subject": “CWC!”}, "attachments": [{"filename": "Golden__MP3s_and_PDFs.zip", "content-type": "application/zip", "size": 26089706}], "recipients": ["sb@reply.sb.com"], "size": 26125372}, "recipient": “sb@reply.sb.com", "id": "BR_4Qv8vTECPlgjFPBOnrQ

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

David Favor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

I put it in Office since I thought Excel might be able to parse JSON.

David Favor

Try highlighting your posted data + hitting CODE in the comment menu bar.

This might help you better visualize your data.

Be sure you're starting with full data, rather than truncated/corrupted data.

Be far easier to work with, if your data is complete/integrous.

aikimark

You can use this regex pattern to parse the JSON and capture the email addresses.

"message": {"headers": {"to": "([^"]*)"

Open in new window