Table: mastodon_my_toot - Query Mastodon Toots using SQL

Mastodon is a decentralized, open-source social network. A Toot on Mastodon is similar to a Tweet on Twitter. It is a message that a user can post, and it can contain text, hashtags, media attachments, and polls.

Table Usage Guide

The mastodon_my_toot table provides insights into the user's own toots within Mastodon. As a social media analyst, explore toot-specific details through this table, including content, media attachments, and associated metadata. Utilize it to uncover information about your toots, such as their reach, the engagement they received, and their overall impact on your Mastodon presence.

Examples

List newest 30 toots posted to my account

Explore the most recent 30 posts made to your account to stay updated with your activity. This query is particularly useful for monitoring your recent posts without having to sift through your entire timeline.

select
  created_at,
  username,
  url,
  content
from
  mastodon_my_toot
limit
  30;

select
  created_at,
  username,
  url,
  content
from
  mastodon_my_toot
limit
  30;

Note: Always use limit or the query will try to read the whole timeline (until max_items is reached).

Classify my recent toots by type

This query is useful to categorize your recent posts on Mastodon into three different types: boosted posts, replies, and original posts. By doing so, it provides a quick overview of your activity patterns on the platform.

with data as (
  select
    case
      when reblog -> 'url' is not null then 'boosted'
      when in_reply_to_account_id is not null then 'in_reply_to'
      else 'original'
    end as type
  from
    mastodon_my_toot
  limit
    200
)
select
  type,
  count(*)
from
  data
group by
  type
order by
  count desc;

with data as (
  select
    case
      when json_extract(reblog, '$.url') is not null then 'boosted'
      when in_reply_to_account_id is not null then 'in_reply_to'
      else 'original'
    end as type
  from
    mastodon_my_toot
  limit
    200
)
select
  type,
  count(*)
from
  data
group by
  type
order by
  count desc;

Count my recent toots by day

Discover the frequency of your recent posts on Mastodon by day. This can help you understand your activity patterns and optimize your posting schedule for better engagement.

with data as (
  select
    to_char(created_at, 'YY-MM-DD') as day
  from
    mastodon_my_toot
  limit
    200
)
select
  day,
  count(*)
from
  data
group by
  day
order by
  day;

with data as (
  select
    strftime('%y-%m-%d', created_at) as day
  from
    mastodon_my_toot
  limit
    200
)
select
  day,
  count(*)
from
  data
group by
  day
order by
  day;

Schema for mastodon_my_toot

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_my_toot