Table: mastodon_toot_direct - Query Mastodon Direct Toots using SQL

Mastodon is a decentralized social network service that allows users to publish anything they want, including links, pictures, text, video. Direct Toots in Mastodon are messages that are sent directly to a user, not visible on any public timeline. They are similar to private messages in other social networks.

Table Usage Guide

The mastodon_toot_direct table provides insights into Direct Toots within Mastodon. As a social media analyst, explore toot-specific details through this table, including sender, content, and associated metadata. Utilize it to uncover information about toots, such as those from specific users, the content of the toots, and the timing of these direct messages.

Examples

Get recent private toots (aka direct messages)

Explore recent private messages on the Mastodon platform to monitor user interactions and ensure a safe, respectful community environment.

select
  created_at,
  username,
  url,
  content
from
  mastodon_toot_direct
limit
  20;

select
  created_at,
  username,
  url,
  content
from
  mastodon_toot_direct
limit
  20;

Enrich DMs with account info

Determine the areas in which direct messages can be enhanced with additional account information. This query can be used to add more context to messages, such as the sender's username or display name, the date and time the message was created, and the account the message was in reply to.

select
  case
    when display_name = '' then username
    else display_name
  end as person,
  to_char(created_at, 'YYYY-MM-DD HH24:MI') as created_at,
  case
    when in_reply_to_account_id is not null then (
      select
        acct
      from
        mastodon_account
      where
        id = in_reply_to_account_id
    )
    else ''
  end as in_reply_to,
  instance_qualified_url,
  content as toot
from
  mastodon_toot_direct;

select
  case
    when display_name = '' then username
    else display_name
  end as person,
  datetime(created_at, 'localtime') as created_at,
  case
    when in_reply_to_account_id is not null then (
      select
        acct
      from
        mastodon_account
      where
        id = in_reply_to_account_id
    )
    else ''
  end as in_reply_to,
  instance_qualified_url,
  content as toot
from
  mastodon_toot_direct;

Schema for mastodon_toot_direct

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account	jsonb	Account for toot author.
account_url	text	Account URL for toot author.
content	text	Content of the toot.
created_at	timestamp with time zone	Timestamp when the toot was created.
display_name	text	Display name for toot author.
followers	bigint	Follower count for toot author.
following	bigint	Following count for toot author.
id	text	ID of the toot.
in_reply_to_account_id	text	If the toot is a reply, the ID of the replied-to toot's account.
instance_qualified_account_url	text	Account URL prefixed with my instance
instance_qualified_reblog_url	text	Url of the reblog (boost) of the toot, prefixed with my instance.
instance_qualified_url	text	URL for the toot, as seen from my instance.
list_id	text	Id for a list that gathers toots.
query	text	Query string to find toots.
reblog	jsonb	Reblog (boost) of the toot.
reblog_content	text	Content of reblog (boost) of the toot.
reblog_server	text	Server of the boosted account.
reblog_username	text	Username of the boosted account.
reblogs_count	bigint	Boost count for toot.
replies_count	bigint	Reply count for toot.
server	text	Server of toot author.
status	jsonb	Raw status
url	text	URL for the toot.
username	text	Username for toot author.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- mastodon

You can pass the configuration to the command with the --config argument:

steampipe_export_mastodon --config '<your_config>' mastodon_toot_direct