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 thewhere
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_countfrom mastodon_accountwhere id = '57523';
select acct, username, display_name, followers_count, following_count, statuses_countfrom mastodon_accountwhere 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_namefrom 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_namefrom 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