turbot/mastodon
steampipe plugin install mastodon

Table: mastodon_account - Query Mastodon Accounts using SQL

Mastodon is a decentralized social network service. It allows users to create their own servers or "instances" and connect with other servers globally, forming a federated social network. Users can create accounts on these instances with unique usernames, profile pictures, display names, and more.

Table Usage Guide

The mastodon_account table provides insights into account details within the Mastodon social network. As a social media analyst, explore account-specific details through this table, including username, display name, followers count, following count, statuses count, and more. Utilize it to uncover information about accounts, such as those with high follower counts, the ratio between followers and following, and the frequency of statuses.

Important Notes

  • You must specify the id column in the where clause to query this table.

Examples

Details for an account

This query is useful for gaining insights into the specifics of a particular user account on the Mastodon platform. It provides a comprehensive overview of the user's account, including their username, display name, and metrics related to their followers, followings, and statuses.

select
acct,
username,
display_name,
followers_count,
following_count,
statuses_count
from
mastodon_account
where
id = '57523';
select
acct,
username,
display_name,
followers_count,
following_count,
statuses_count
from
mastodon_account
where
id = '57523';

Recent replies in the home timeline

Explore recent interactions on your home timeline by identifying the users who have replied to your posts. This can help you understand your audience's engagement and participation in your discussions.

with toots as (
select
*
from
mastodon_toot_home
where
in_reply_to_account_id is not null
limit
10
)
select
t.username,
t.display_name,
a.username as in_reply_to_username,
a.display_name as in_reply_to_display_name
from
toots t
join mastodon_account a on a.id = t.in_reply_to_account_id;
with toots as (
select
*
from
mastodon_toot_home
where
in_reply_to_account_id is not null
limit
10
)
select
t.username,
t.display_name,
a.username as in_reply_to_username,
a.display_name as in_reply_to_display_name
from
toots t
join mastodon_account a on a.id = t.in_reply_to_account_id;

Schema for mastodon_account

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The account ID.
accttextusername@server for the account.
created_attimestamp with time zoneTimestamp when the account was created.
display_nametextDisplay name for the account.
followers_countbigintNumber of followers for the account.
following_countbigintNumber of accounts this account follows.
idtext=ID of the account.
instance_qualified_account_urltextAccount URL prefixed with my instance.
notetextDescription of the account.
servertextServer for the account.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statuses_countbigintToots from this account.
urltextURL for the account.
usernametextUsername for the account.

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_account