turbot/mastodon
steampipe plugin install mastodon

Table: mastodon_toot_local - Query Mastodon Local Toots using SQL

Mastodon is a decentralized, open-source social network. A 'Toot' in Mastodon is a post made by a user, similar to a 'Tweet' in Twitter. 'Local Toots' refer to the posts made by users on the local instance of a Mastodon server.

Table Usage Guide

The mastodon_toot_local table provides insights into the local toots within a Mastodon server. As a system administrator or a community manager, explore toot-specific details through this table, including content, author details, and associated metadata. Utilize it to uncover information about toots, such as their reach, the interactions they've received, and the activity of users on the local instance.

Examples

Get recent toots on the local timeline

Explore the latest posts on the local timeline to stay updated on recent activities and discussions. This is particularly useful for quickly catching up with the most recent happenings in your network.

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

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

Hashtag frequency for recent toots on the local timeline

Explore the popularity of various hashtags in recent posts on the local timeline. This can help identify trending topics and gauge user engagement within the community.

with data as (
select
regexp_matches(content, '(#[^#\s]+)', 'g') as hashtag
from
mastodon_toot_local
limit
100
)
select
hashtag,
count(*)
from
data
group by
hashtag
order by
count desc,
hashtag;
Error: SQLite does not support regular expressions in the same way as PostgreSQL.

Schema for mastodon_toot_local

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accountjsonbAccount for toot author.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The account ID.
account_urltextAccount URL for toot author.
contenttextContent of the toot.
created_attimestamp with time zoneTimestamp when the toot was created.
display_nametextDisplay name for toot author.
followersbigintFollower count for toot author.
followingbigintFollowing count for toot author.
idtextID of the toot.
in_reply_to_account_idtextIf the toot is a reply, the ID of the replied-to toot's account.
instance_qualified_account_urltextAccount URL prefixed with my instance
instance_qualified_reblog_urltextUrl of the reblog (boost) of the toot, prefixed with my instance.
instance_qualified_urltextURL for the toot, as seen from my instance.
list_idtextId for a list that gathers toots.
querytextQuery string to find toots.
reblogjsonbReblog (boost) of the toot.
reblog_contenttextContent of reblog (boost) of the toot.
reblog_servertextServer of the boosted account.
reblog_usernametextUsername of the boosted account.
reblogs_countbigintBoost count for toot.
replies_countbigintReply count for toot.
servertextServer of toot author.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statusjsonbRaw status
urltextURL for the toot.
usernametextUsername 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_local