I was determined to write my Mastodon #introduction today. To get started I used the tag search in the dashboard I’m building.
IDGThe idea was to look at a bunch of other #introduction posts to get a feel for how mine should go. When you search specifically for hashtags, the Mastodon search API returns this information.
"hashtags": [
{
"name": "introduction",
"url": "https://mastodon.social/tags/introduction",
"history": [
{
"day": "1574553600",
"uses": "10",
"accounts": "9"
},
// ...
]
},
A first version of the dashboard, having only this data to work with, just listed the names of tags matching the search term along with corresponding URLs. Here was the initial query.
select name, url from mastodon_search_hashtag where query = 'introduction'
That produced a list of links, like https://mastodon.social/tags/introduction, to home pages for variants of the tag. These are useful links! Each goes to a page where you can see who is posting to the tag.
To make this view slightly more useful, I tapped the third element of the API response, history, in a revised query.
with data as (
select
name,
url,
( jsonb_array_elements(history) ->> 'uses' )::int as uses
from
mastodon_search_hashtag
where
query = 'introduction'
)
select
name,
url,
sum(uses)
from
data
group by
name, url
order by
sum desc
These results help me decide which variant to use.
+-------------------+---------------------------------------------------+------+ | name | url | sum | +-------------------+---------------------------------------------------+------+ | introduction | https://mastodon.social/tags/introduction | 1816 | | introductions | https://mastodon.social/tags/introductions | 218 | | introductionpost | https://mastodon.social/tags/introductionpost | 19 | | introductionfr | https://mastodon.social/tags/introductionfr | 6 |
But I still need to visit each link’s page to explore how it’s being used. It would be nice to surface more context in the dashboard, and I found a nifty way to do it, but first let’s dwell on the revised query for a minute. Postgres’s JSON features are powerful and it’s often a challenge (at least for me) to visualize how they work.
The Postgres jsonb_array_elements() function is what’s called a set-returning function. Here it unpacks Postgres’s JSON representation of the list of history structures returned from the Mastodon API. In its simplest form, the function call jsonb_array_elements(history) produces a temporary table with per-tag, per-day data.
select name, jsonb_array_elements(history) as history from mastodon_search_hashtag where query = 'introduction'
+--------------------------------+----------------------------------------------------+
| name | history |
+--------------------------------+----------------------------------------------------+
| introduction | {"accounts":"16","day":"1670371200","uses":"19"} |
| introduction | {"accounts":"250","day":"1670284800","uses":"269"} |
| introduction | {"accounts":"259","day":"1670198400","uses":"274"} |
| introduction | {"accounts":"253","day":"1670112000","uses":"270"} |
| introduction | {"accounts":"245","day":"1670025600","uses":"269"} |
| introduction | {"accounts":"345","day":"1669939200","uses":"383"} |
| introduction | {"accounts":"307","day":"1669852800","uses":"339"} |
| introductionsfr | {"accounts":"0","day":"1670371200","uses":"0"} |
| introductionsfr | {"accounts":"0","day":"1670284800","uses":"0"} |
| introductionsfr | {"accounts":"0","day":"1670198400","uses":"0"} |
| introductionsfr | {"accounts":"0","day":"1670112000","uses":"0"} |
| introductionsfr | {"accounts":"0","day":"1670025600","uses":"0"} |
history is a JSONB column that holds an object with three fields. The revised query uses Postgres’s JSON indexing operator ->> to reach into that object and hoist the number of daily uses into its own column, so it can be the target of a SQL SUM function.
OK, ready for the nifty solution? Recall that https://mastodon.social/tags/introduction is the home page for that variant of the tag. There you can see introduction posts from people using the tag. Those posts typically include other tags. In the dashboard shown above you can see that Kathy Nickels is using these: #Music #Art #Equestrian #Nature #Animals. The tags appear in her introduction post.
IDGI didn’t immediately see how to capture them for use in the dashboard. Then I remembered that certain classes of Mastodon page have corresponding RSS feeds, and wondered if the tag pages are members of one such class. Sure enough they are, and https://mastodon.social/tags/introduction.rss is a thing. That link, formed by tacking .rss onto the base URL, provides the extra context I was looking for. Here’s the final version of the query.
with data as (
select
name,
url,
( jsonb_array_elements(history) ->> 'uses' )::int as uses
from
mastodon_search_hashtag
where
query = 'introduction'
),
uses as (
select
name,
url || '.rss' as feed_link,
sum(uses) as recent_uses
from
data
group
by connection, name, url
)
select
u.name,
r.guid as link,
to_char(r.published, 'YYYY-MM-DD') as published,
r.categories
from
uses u
join
rss_item r
on
r.feed_link = u.feed_link
where
recent_uses > 1
order by
recent_uses desc, published desc
)
The new ingredients, courtesy of the RSS feed, are guid, which links to an individual introduction like Kathy’s; published, which is the day the introduction appeared; and categories, which has the tags used in the introduction post. Sweet! Now I can scan the dashboard to get a sense of which introductions I want to check out.
The first three queries use the Steampipe plugin for Mastodon, and in particular its mastodon_search_hashtag table, which encapsulates the Mastodon API for searching tags. The final version joins that table with the rss_item table provided by the RSS plugin, using the common base URL as the basis of the join.
This delights me in so many ways. When the blogosphere first emerged in the early 2000s, some of us discovered that the RSS protocol was capable of far more than just delivering feeds to RSS readers. The other new hot protocol in that era was XML web services. As an InfoWorld analyst I was supposed to be cheering the latter as an enterprise-grade technology, but I couldn’t help noticing that RSS kept turning out to be a great way to move data between cooperating systems. That’s always been true, and I love how this example reminds us that it’s still true.
I’m equally delighted to show how Steampipe enables this modern exercise in RSS-powered integration. Steampipe was, originally, an engine for mapping results from JSON API endpoints to SQL tables. Over time, though, it has broadened its view of what constitutes an API. You can use Steampipe to query CSV files, or Terraform files, or—as we see here—RSS feeds. Data comes in all kinds of flavors. Steampipe abstracts those differences and brings all the flavors into a common space where you can reason over them using SQL.
And finally, it’s just wonderful to be at the intersection of Mastodon, Steampipe, and RSS in this remarkable moment. I’ll readily admit that nostalgia is a factor. But RSS did bust things wide open 20 years ago, Mastodon’s doing that now, and I love that RSS can help it happen again.
Now I need to write that #introduction!
This series:
- Autonomy, packet size, friction, fanout, and velocity
- Build a Mastodon dashboard with Steampipe
- Browsing the fediverse
- A Bloomberg terminal for Mastodon
- Create your own Mastodon UX
- Lists and people on Mastodon
- How many people in my Mastodon feed also tweeted today?
- Instance-qualified Mastodon URLs
- Mastodon relationship graphs
- Working with Mastodon lists
- Images considered harmful (sometimes)
- Mapping the wider fediverse
- Protocols, APIs, and conventions
- News in the fediverse