Table: mastodon_toot_federated - Query Mastodon Federated Toots using SQL

Mastodon Federated Toots are posts that are shared across different instances of the Mastodon social network. This feature enables users to interact with posts from other Mastodon instances, extending their reach beyond their own instance. It plays a crucial role in the decentralized nature of Mastodon, allowing for a diverse and wide-ranging discourse.

Table Usage Guide

The mastodon_toot_federated table provides insights into Federated Toots within the Mastodon social network. As a social media analyst, explore toot-specific details through this table, including content, reach, and associated metadata. Utilize it to uncover information about toots, such as those with wide reach, the relationships between different toots, and the verification of content distribution.

Examples

Get recent toots on the federated timeline

Explore the recent posts on the federated timeline to gain insights into the latest discussions and trends. This can be beneficial for staying updated with current topics or tracking the popularity of certain subjects.

select
  created_at,
  username,
  url,
  content
from
  mastodon_toot_federated
limit
  30;

select
  created_at,
  username,
  url,
  content
from
  mastodon_toot_federated
limit
  30;

Note: Always use limit or the query will try to read the whole timeline (until max_items is reached).

Count replies among recent toots on the federated timeline

Analyze the interaction on recent posts in the federated timeline by counting the number of replies. This is useful for gauging the level of engagement and interaction within your community.

with data as (
  select
    in_reply_to_account_id is not null as is_reply
  from
    mastodon_toot_federated
  limit
    100
)
select
  count(*) as replies
from
  data
where
  is_reply;

with data as (
  select
    in_reply_to_account_id is not null as is_reply
  from
    mastodon_toot_federated
  limit
    100
)
select
  count(*) as replies
from
  data
where
  is_reply;

Server frequency for recent toots on the federated timeline

Discover which servers have the most recent posts on the federated timeline, providing insights into the most active servers in the network. This can help you understand where the majority of recent activity is originating from.

with data as (
  select
    server
  from
    mastodon_toot_federated
  limit
    100
)
select
  server,
  count(*)
from
  data
group by
  server
order by
  count desc;

with data as (
  select
    server
  from
    mastodon_toot_federated
  limit
    100
)
select
  server,
  count(*)
from
  data
group by
  server
order by
  count(*) desc;

Schema for mastodon_toot_federated

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	jsonb		Account for toot author.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The account ID.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	jsonb		Raw status
url	text		URL for the toot.
username	text		Username 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_federated