turbot/mastodon

GitHub
steampipe plugin install mastodonsteampipe plugin install mastodon

Table: mastodon_toot_list

Represents a toot on your list timeline.

The mastodon_toot_list table can be used to query information about any toot on your list, and you must specify the list_id in the where or join clause using the list_id column.

Examples

Get recent toots on a list's timeline

select
created_at,
username,
url,
content
from
mastodon_toot_list
where
list_id = '42994'
limit
30;

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

with 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
where
list_id = '42994'
and 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;

.inspect mastodon_toot_list

Represents a toot on your list timeline.

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accountjsonbAccount for toot author.
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_idtextId 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.
statusjsonbRaw status
urltextURL for the toot.
usernametextUsername for toot author.