Table: mastodon_search_account - Query Mastodon Accounts using SQL

Mastodon is a free and open-source self-hosted social networking service. It allows anyone to host their own server node in the network, and its various separately operated user bases are federated across many different servers. These servers are connected as a federated social network, allowing users from different servers to interact with each other seamlessly.

Table Usage Guide

The mastodon_search_account table provides insights into Mastodon Accounts. As a social media analyst, explore account-specific details through this table, including username, display name, created at date, followers count, following count, statuses count and more. Utilize it to uncover information about accounts, such as those with high followers count, the activity level of accounts, and the verification of account details.

Important Notes

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

Examples

Search for accounts matching alice

Discover the segments that contain accounts matching a specific term to analyze user engagement and activity. This can be useful in understanding the popularity and influence of certain users within a platform.

select
  acct,
  username,
  display_name,
  followers_count,
  following_count,
  statuses_count
from
  mastodon_search_account
where
  query = 'alice';

select
  acct,
  username,
  display_name,
  followers_count,
  following_count,
  statuses_count
from
  mastodon_search_account
where
  query = 'alice';

Note: Finds the search term case-insensitively in the username or display_name columns.

Show my relationships to accounts matching alice

Determine your interactions with specific accounts, in this case 'alice', by analyzing the mutual following status, account creation date, follower count, number of posts, and account notes. This can help you understand your engagement with these accounts and manage your social media presence effectively.

with data as (
  select
    id,
    instance_qualified_account_url,
    username || ', ' || display_name as person,
    to_char(created_at, 'YYYY-MM-DD') as created_at,
    followers_count,
    following_count,
    statuses_count as toots,
    note
  from
    mastodon_search_account
  where
    query = 'alice'
  order by
    person
  limit
    20
)
select
  d.instance_qualified_account_url,
  d.person,
  case
    when r.following then '✔️'
    else ''
  end as i_follow,
  case
    when r.followed_by then '✔️'
    else ''
  end as follows_me,
  d.created_at,
  d.followers_count as followers,
  d.following_count as following,
  d.toots,
  d.note
from
  data d
  join mastodon_relationship r on d.id = r.id;

with data as (
  select
    id,
    instance_qualified_account_url,
    username || ', ' || display_name as person,
    strftime('%Y-%m-%d', created_at) as created_at,
    followers_count,
    following_count,
    statuses_count as toots,
    note
  from
    mastodon_search_account
  where
    query = 'alice'
  order by
    person
  limit
    20
)
select
  d.instance_qualified_account_url,
  d.person,
  case
    when r.following then '✔️'
    else ''
  end as i_follow,
  case
    when r.followed_by then '✔️'
    else ''
  end as follows_me,
  d.created_at,
  d.followers_count as followers,
  d.following_count as following,
  d.toots,
  d.note
from
  data d
  join mastodon_relationship r on d.id = r.id;

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_search_account