json_read.sql

/*
  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": "[email protected]",
  "sourceArn": "arn:aws:ses:eu-west-1:056345409346:identity/example.com",
  "sendingAccountId": "067935456346",
  "messageId": "01020166456a7592-0d87ab1d-ada6-4c0d-833f-30b066779ac6-000000",
  "destination": [
    "[email protected]"
  ],
  "headersTruncated": false,
  "headers": [
    {
      "name": "Content-Type",
      "value": "text/html"
    },
    {
      "name": "From",
      "value": "Example <[email protected]>"
    },
    {
      "name": "To",
      "value": "[email protected]"
    },
    {
      "name": "Subject",
      "value": "Payment Refund"
    },
    {
      "name": "Message-ID",
      "value": "<[email protected]>"
    },
    {
      "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 <[email protected]>"
    ],
    "date": "Thu, 27 Sep 2018 05:57:24 +0000",
    "to": [
      "[email protected]"
    ],
    "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'