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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The account ID.
acct	text		username@server for the account.
created_at	timestamp with time zone		Timestamp when the account was created.
display_name	text		Display name for the account.
followers_count	bigint		Number of followers for the account.
following_count	bigint		Number of accounts this account follows.
id	text	=	ID of the account.
instance_qualified_account_url	text		Account URL prefixed with my instance.
note	text		Description of the account.
server	text		Server for the account.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
statuses_count	bigint		Toots from this account.
url	text		URL for the account.
username	text		Username 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