turbot/mastodon
steampipe plugin install mastodon

Table: mastodon_toot_list - Query Mastodon Toots 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 users post short messages, called 'toots' for others to see.

Table Usage Guide

The mastodon_toot_list table provides insights into the 'toots' or posts made by users on the Mastodon platform. As a data analyst or social media manager, explore toot-specific details through this table, including content, timestamps, and associated metadata. Utilize it to uncover information about toots, such as their reach, the interactions they have generated, and the context of their creation.

Important Notes

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

Examples

Get recent toots on a list's timeline

Discover the latest posts from a specific user list on a social media platform. This can be useful for monitoring recent activity or trends within a particular group.

with list_info as (
select
id
from
mastodon_my_list
limit
1
)
select
created_at,
username,
url,
content
from
mastodon_toot_list m
join list_info l on m.list_id = l.id
limit
10
with list_info as (
select
id
from
mastodon_my_list
limit
1
)
select
m.created_at,
m.username,
m.url,
m.content
from
mastodon_toot_list m
join list_info l on m.list_id = l.id
limit
10;

Get recent original toots on a list's timeline, at most one per person per day

This query helps in analyzing the recent original posts on a specific list's timeline, restricting it to a single post per user per day. The practical application of this query is to maintain a concise and diverse feed by eliminating repetitive posts from the same user within a day.

with list_info as (
select
id
from
mastodon_my_list
limit
1
), data as (
select
list_id,
to_char(created_at, 'YYYY-MM-DD') as day,
case
when display_name = '' then username
else display_name
end as person,
instance_qualified_url as url,
substring(
content
from
1 for 200
) as toot
from
mastodon_toot_list m
join list_info l on m.list_id = l.id
where
reblog -> 'url' is null -- only original posts
and in_reply_to_account_id is null -- only original posts
limit
40
)
select
distinct on (person, day) -- only one per person per day
day,
person,
toot,
url
from
data
order by
day desc,
person;
with list_info as (
select
id
from
mastodon_my_list
limit
1
), data as (
select
m.list_id,
strftime('%Y-%m-%d', m.created_at) as day,
case
when m.display_name = '' then m.username
else m.display_name
end as person,
m.instance_qualified_url as url,
substr(m.content, 1, 200) as toot
from
mastodon_toot_list m
join list_info l on m.list_id = l.id
where
json_extract(m.reblog, '$.url') is null -- only original posts
and m.in_reply_to_account_id is null -- only original posts
limit
40
)
select
day,
person,
toot,
url
from
data
group by
person,
day
order by
day desc,
person;

Schema for mastodon_toot_list

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accountjsonbAccount for toot author.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The account ID.
account_urltextAccount URL for toot author.
contenttextContent of the toot.
created_attimestamp with time zoneTimestamp when the toot was created.
display_nametextDisplay name for toot author.
followersbigintFollower count for toot author.
followingbigintFollowing count for toot author.
idtextID of the toot.
in_reply_to_account_idtextIf the toot is a reply, the ID of the replied-to toot's account.
instance_qualified_account_urltextAccount URL prefixed with my instance
instance_qualified_reblog_urltextUrl of the reblog (boost) of the toot, prefixed with my instance.
instance_qualified_urltextURL for the toot, as seen from my instance.
list_idtext=Id for a list that gathers toots.
querytextQuery string to find toots.
reblogjsonbReblog (boost) of the toot.
reblog_contenttextContent of reblog (boost) of the toot.
reblog_servertextServer of the boosted account.
reblog_usernametextUsername of the boosted account.
reblogs_countbigintBoost count for toot.
replies_countbigintReply count for toot.
servertextServer of toot author.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statusjsonbRaw status
urltextURL for the toot.
usernametextUsername for toot author.

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_toot_list