mastodon_accountmastodon_domain_blockmastodon_followermastodon_followingmastodon_list_accountmastodon_my_accountmastodon_my_followermastodon_my_followingmastodon_my_listmastodon_my_tootmastodon_notificationmastodon_peermastodon_ratemastodon_relationshipmastodon_rulemastodon_search_accountmastodon_search_hashtagmastodon_search_tootmastodon_servermastodon_toot_directmastodon_toot_favouritemastodon_toot_federatedmastodon_toot_homemastodon_toot_listmastodon_toot_localmastodon_weekly_activity
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, contentfrom mastodon_toot_listwhere 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, urlfrom dataorder by day desc, person;
.inspect mastodon_toot_list
Represents a toot on your list timeline.
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
account | jsonb | Account for toot author. |
account_url | text | Account URL for toot author. |
content | text | Content of the toot. |
created_at | timestamp with time zone | Timestamp when the toot was created. |
display_name | text | Display name for toot author. |
followers | bigint | Follower count for toot author. |
following | bigint | Following count for toot author. |
id | text | ID of the toot. |
in_reply_to_account_id | text | If the toot is a reply, the ID of the replied-to toot's account. |
instance_qualified_account_url | text | Account URL prefixed with my instance |
instance_qualified_reblog_url | text | Url of the reblog (boost) of the toot, prefixed with my instance. |
instance_qualified_url | text | URL for the toot, as seen from my instance. |
list_id | text | Id for a list that gathers toots. |
query | text | Query string to find toots. |
reblog | jsonb | Reblog (boost) of the toot. |
reblog_content | text | Content of reblog (boost) of the toot. |
reblog_server | text | Server of the boosted account. |
reblog_username | text | Username of the boosted account. |
reblogs_count | bigint | Boost count for toot. |
replies_count | bigint | Reply count for toot. |
server | text | Server of toot author. |
status | jsonb | Raw status |
url | text | URL for the toot. |
username | text | Username for toot author. |