Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Table (named=> 'event_head') has one column (named=> 'data_dumb') as json which contains below json object.
- We want to read data from that JSON.
- ==================== EXAMPLE JSON Object ====================
- {
- "timestamp": "2018-09-27T05:57:25.010Z",
- "source": "adc@example.com",
- "sourceArn": "arn:aws:ses:eu-west-1:056345409346:identity/example.com",
- "sendingAccountId": "067935456346",
- "messageId": "01020166456a7592-0d87ab1d-ada6-4c0d-833f-30b066779ac6-000000",
- "destination": [
- "xyz@example.com"
- ],
- "headersTruncated": false,
- "headers": [
- {
- "name": "Content-Type",
- "value": "text/html"
- },
- {
- "name": "From",
- "value": "Example <adc@example.com>"
- },
- {
- "name": "To",
- "value": "xyz@example.com"
- },
- {
- "name": "Subject",
- "value": "Payment Refund"
- },
- {
- "name": "Message-ID",
- "value": "<89fa9068-4ac5-b46f-7091-96b278d35ef1@b2x.com>"
- },
- {
- "name": "Content-Transfer-Encoding",
- "value": "quoted-printable"
- },
- {
- "name": "Date",
- "value": "Thu, 27 Sep 2018 05:57:24 +0000"
- },
- {
- "name": "MIME-Version",
- "value": "1.0"
- }
- ],
- "commonHeaders": {
- "from": [
- "Example <adc@example.com>"
- ],
- "date": "Thu, 27 Sep 2018 05:57:24 +0000",
- "to": [
- "xyz@example.com"
- ],
- "messageId": "01020166199a7592-0d87ab1d-ada6-4c0d-833f-30b013579ac6-000000",
- "subject": "Payment Refund"
- },
- "tags": {
- "ses:operation": [
- "SendRawEmail"
- ],
- "ses:configuration-set": [
- "Email-Stat-Config-Set"
- ],
- "ses:source-ip": [
- "34.006.155.219"
- ],
- "ses:from-domain": [
- "b2x.com"
- ],
- "ses:caller-identity": [
- "email-stat"
- ]
- },
- "eventType": "Send",
- "time": "2018-09-27T05:57:25.010Z",
- "send": {
- }
- }
- */
- SELECT e.value, d.value, eh.*
- FROM event_head eh
- JOIN json_array_elements((data_dump->>'headers')::json) e ON TRUE
- JOIN json_each_text(e::json) d ON TRUE
- WHERE e->>'name' = 'Subject';
- /* ========================== OTHER ========================= */
- SELECT mo.id, mo.model_code, mo.device_type--, e.value->>'model code' --, d->'model code'
- FROM mst_model mo,
- json_array_elements('[{"model code": "950A216200U4", "device type": "phone"}, {"model code": "95A12B0D0009", "device type": "phone"}]'::json) e
- --JOIN json_each_text(e::json) d ON TRUE
- WHERE mo.model_code = e.value->>'model code' AND mo.device_type = e.value->>'device type'
Add Comment
Please, Sign In to add comment