Table: mastodon_list_account - Query Mastodon List Accounts using SQL

Mastodon is a decentralized, open source social network. It is a part of the wider Fediverse, allowing its users to also interact with users on different open platforms that support the same protocol. List Accounts in Mastodon are a way to organize and manage multiple accounts under a specific list, providing a streamlined view of the chosen accounts.

Table Usage Guide

The mastodon_list_account table provides insights into List Accounts within Mastodon. As a social media manager or a digital marketer, explore account-specific details through this table, including account metadata, list associations, and other related information. Utilize it to uncover information about accounts, such as those with certain characteristics, the relationships between accounts, and the verification of metadata.

Important Notes

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

Examples

List members of a Mastodon list

Explore which members belong to a specific Mastodon list, gaining insights into their usernames and display names for better user management and communication strategies.

with list_id as (
  select
    id
  from
    mastodon_my_list
  limit
    1
)
select
  url,
  username,
  display_name
from
  mastodon_list_account a
  join list_id l on a.list_id = l.id

with list_id as (
  select
    id
  from
    mastodon_my_list
  limit
    1
)
select
  a.url,
  a.username,
  a.display_name
from
  mastodon_list_account a
  join list_id l on a.list_id = l.id;

List details for members of all my Mastodon lists

Explore which members belong to your Mastodon lists, gaining insights into their display names, server details, and follower and following counts. This can help assess the popularity and reach of each member within your lists.

select
  l.title,
  a.display_name,
  a.server,
  a.followers_count,
  a.following_count
from
  mastodon_my_list l
  join mastodon_list_account a on l.id = a.list_id;

select
  l.title,
  a.display_name,
  a.server,
  a.followers_count,
  a.following_count
from
  mastodon_my_list l
  join mastodon_list_account a on l.id = a.list_id;

Count how many of the accounts I follow are assigned (and not assigned) to lists

Explore the organization of your followed accounts on Mastodon by determining how many are assigned to lists versus those that are not. This can help manage your follow list by identifying areas for potential reorganization or cleanup.

with list_account as (
  select
    a.id,
    l.title as list
  from
    mastodon_my_list l
    join mastodon_list_account a on l.id = a.list_id
),
list_account_follows as (
  select
    list
  from
    mastodon_my_following
    left join list_account using (id)
)
select
  'Follows listed' as label,
  count(*)
from
  list_account_follows
where
  list is not null
union
select
  'Follows unlisted' as label,
  count(*)
from
  list_account_follows
where
  list is null;

with list_account as (
  select
    a.id,
    l.title as list
  from
    mastodon_my_list l
    join mastodon_list_account a on l.id = a.list_id
),
list_account_follows as (
  select
    list
  from
    mastodon_my_following
    left join list_account on mastodon_my_following.id = list_account.id
)
select
  'Follows listed' as label,
  count(*)
from
  list_account_follows
where
  list is not null
union
select
  'Follows unlisted' as label,
  count(*)
from
  list_account_follows
where
  list is null;

Schema for mastodon_list_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.
list_id	text	=	List ID for account.
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_list_account