turbot/mastodon

GitHub
steampipe plugin install mastodonsteampipe plugin install mastodon

Table: mastodon_list_account

Represents an account of a list of yours.

The mastodon_list_account table can be used to query information about any account, and you must specify the list_id in the where or join clause using the list_id column.

Examples

List members of a Mastodon list

select
url,
username,
display_name
from
mastodon_list_account
where
list_id = '42994';

List details for members of all my Mastodon 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;

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

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;

.inspect mastodon_list_account

Represents an account of a list of yours.

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
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.
idtextID of the account.
instance_qualified_account_urltextAccount URL prefixed with my instance.
list_idtextList ID for account.
notetextDescription of the account.
servertextServer for the account.
statuses_countbigintToots from this account.
urltextURL for the account.
usernametextUsername for the account.