messages (view)

13,023 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: sent_at (date)

Next page

CREATE VIEW messages AS
SELECT
  e.stream_ordering                                        AS seq,
  e.event_id,
  e.room_id,
  e.origin_server_ts                                       AS ts,
  datetime(e.origin_server_ts / 1000, 'unixepoch', 'localtime') AS sent_at,
  e.sender,
  p.displayname                                            AS sender_name,
  CASE
    WHEN e.sender LIKE '@whatsapp\_%'  ESCAPE '\' THEN 'whatsapp'
    WHEN e.sender LIKE '@signal\_%'    ESCAPE '\' THEN 'signal'
    WHEN e.sender LIKE '@telegram\_%'  ESCAPE '\' THEN 'telegram'
    WHEN e.sender LIKE '@gmessages\_%' ESCAPE '\' THEN 'sms'
    WHEN e.sender LIKE '@meta\_%'      ESCAPE '\' THEN 'messenger'
    WHEN e.sender LIKE '@slack\_%'     ESCAPE '\' THEN 'slack'
    ELSE 'local'
  END                                                      AS network,
  json_extract(ej.json, '$.content.msgtype')               AS msgtype,
  json_extract(ej.json, '$.content.body')                  AS body,
  json_extract(ej.json, '$.content.url')                   AS url,
  -- With an explicit filename, body is the caption that came alongside the
  -- file; without one the spec says body *is* the filename.
  json_extract(ej.json, '$.content.filename')              AS filename,
  json_extract(ej.json, '$.content.info.size')             AS size,
  json_extract(ej.json, '$.content.info.mimetype')         AS mimetype,
  json_extract(ej.json, '$.content."m.relates_to"."m.in_reply_to".event_id') AS reply_to
FROM events e
JOIN event_json ej ON ej.event_id = e.event_id
LEFT JOIN profiles p ON p.full_user_id = e.sender
WHERE e.type = 'm.room.message'
  AND e.rejection_reason IS NULL
  AND NOT e.outlier;