Table: imap_message
Query messages from a given mailbox.
All queries are against a single mailbox, chosen in this order of precedence:
- A
where mailbox = 'INBOX'
qualifier in the query. - The
mailbox
config setting inimap.spc
. - Default is
INBOX
.
Examples
List messages from the default mailbox (e.g. INBOX)
select *from imap_message
List messages from a specific mailbox
select *from imap_messagewhere mailbox = '[Gmail]/Starred'
Find messages greater than 1MB in size
select timestamp, from_email, subject, sizefrom imap_messagewhere size > 1000000order by size desc
List messages received between 7 and 14 days ago
select timestamp, from_email, subjectfrom imap_messagewhere timestamp > current_timestamp - interval '14 days' and timestamp < current_timestamp - interval '7 days'order by timestamp
Find messages from a given address
select timestamp, from_email, subjectfrom imap_messagewhere from_email = 'jim@dundermifflin.com'
Search drafts for messages with a keyword
select timestamp, from_email, subjectfrom imap_messagewhere mailbox = '[Gmail]/Drafts' and query = 'keyword'
List all attachments on Starred messages
select m.timestamp, m.from_email, m.subject, a ->> 'file_name' as attachment_filename, a ->> 'content_type' as attachment_content_typefrom imap_message as m, jsonb_array_elements(attachments) as awhere mailbox = '[Gmail]/Starred'
.inspect imap_message
Messages in IMAP.
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
attachments | jsonb | All parts having a Content-Disposition of attachment. |
bcc_addresses | jsonb | Array of BCC addresses. |
body_html | text | HTML body of the message. |
body_text | text | Text body of the message. |
cc_addresses | jsonb | Array of CC addresses. |
embedded_files | jsonb | All parts having a Content-Disposition of inline. |
errors | jsonb | Errors returned while parsing the email. |
flags | jsonb | Flags set on the message. |
from_addresses | jsonb | Array of From addresses. |
from_email | text | Email address, in lower case, of the first (and usually only) mailbox in the From header. |
headers | jsonb | Full set of headers defined in the message. |
in_reply_to | jsonb | Array of message IDs that this message is a reply to. |
mailbox | text | Mailbox queried for messages. |
message_id | text | Unique message identifier that refers to a particular version of a particular message. |
query | text | Search query to match messages. |
seq_num | bigint | Sequence number of the message. |
size | bigint | Size in bytes of the message. |
subject | text | Subject of the message. |
timestamp | timestamp with time zone | Time when the message was sent. |
to_addresses | jsonb | Array of To addresses. |