I used to be decided to jot down my #intro to Mastodon at present. To get began, I used the tag search on the board I am creating.
IDGThe thought was to have a look at a bunch of different #intro posts to get an concept of what mine ought to seem like. While you particularly seek for hashtags, the Mastodon Search API returns this info.
"hashtags": [
"name": "introduction",
"url": "https://mastodon.social/tags/introduction",
"history": [
"day": "1574553600",
"uses": "10",
"accounts": "9"
,
// ...
]
,
An early model of the dashboard, which solely had this information to work with, solely listed the tag names that matched the search time period together with the corresponding URLs. Right here was the preliminary question.
choose
identify,
url
from
mastodon_search_hashtag
the place
question = 'introduction'
That produced an inventory of hyperlinks, like https://mastodon.social/tags/introduction, to touchdown pages for variants of the tag. These are helpful hyperlinks! Every one goes to a web page the place you may see who’s posting to the tag.
To make this view a bit extra helpful, I touched on the third aspect of the API response, historical pastin a revised question.
with information as (
choose
identify,
url,
( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
from
mastodon_search_hashtag
the place
question = 'introduction'
)
choose
identify,
url,
sum(makes use of)
from
information
group by
identify, url
order by
sum desc
These outcomes assist me resolve which variant to make use of.
+-------------------+---------------------------------------------------+------+
| identify | 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 |
However I nonetheless want to go to every hyperlink’s web page to discover the way it’s getting used. It will be good to indicate extra context on the dashboard, and I discovered a nifty approach to try this, however first let’s dwell on the revised question for a minute. Postgres’ JSON capabilities are highly effective, and it is typically difficult (a minimum of for me) to visualise how they work.
the postgres jsonb_array_elements() perform is what is known as set return perform. Right here you unzip the Postgres JSON illustration of the record of historical past constructions returned by the Mastodon API. In its easiest type, the perform name jsonb_array_elements(historical past) produces a short lived desk with information by tag and by day.
choose
identify,
jsonb_array_elements(historical past) as historical past
from
mastodon_search_hashtag
the place
question = 'introduction'
+--------------------------------+----------------------------------------------------+
| identify | historical past |
+--------------------------------+----------------------------------------------------+
| introduction | "accounts":"16","day":"1670371200","makes use of":"19" |
| introduction | "accounts":"250","day":"1670284800","makes use of":"269" |
| introduction | "accounts":"259","day":"1670198400","makes use of":"274" |
| introduction | "accounts":"253","day":"1670112000","makes use of":"270" |
| introduction | "accounts":"245","day":"1670025600","makes use of":"269" |
| introduction | "accounts":"345","day":"1669939200","makes use of":"383" |
| introduction | "accounts":"307","day":"1669852800","makes use of":"339" |
| introductionsfr | "accounts":"0","day":"1670371200","makes use of":"0" |
| introductionsfr | "accounts":"0","day":"1670284800","makes use of":"0" |
| introductionsfr | "accounts":"0","day":"1670198400","makes use of":"0" |
| introductionsfr | "accounts":"0","day":"1670112000","makes use of":"0" |
| introductionsfr | "accounts":"0","day":"1670025600","makes use of":"0" |
historical past is a JSONB column that accommodates an object with three fields. The revised question makes use of the Postgres JSON indexing operator ->> to get to that object and lift the variety of each day makes use of by itself column, so it may be focused by an SQL SUM perform.
OK, prepared for the intelligent answer? Do not forget that https://mastodon.social/tags/introduction is the house web page for that tag variant. There you may see intro posts from folks utilizing the hashtag. These posts typically embrace different tags. On the board proven above, you may see that Kathy Nickels is sporting these: #Music #Artwork #Equestrian #Nature #Animals. The tags seem in her intro submit.
IDGI did not instantly see tips on how to seize them to be used on the board. Then I remembered that sure courses of Mastodon pages have corresponding RSS feeds, and puzzled if tag pages are members of a kind of courses. Certainly they’re, and https://mastodon.social/tags/introduction.rss is one factor. That bond, shaped by tacking .rss within the base URL, it offers the extra context you have been in search of. That is the ultimate model of the question.
with information as (
choose
identify,
url,
( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
from
mastodon_search_hashtag
the place
question = 'introduction'
),
makes use of as (
choose
identify,
url || '.rss' as feed_link,
sum(makes use of) as recent_uses
from
information
group
by connection, identify, url
)
choose
u.identify,
r.guid as hyperlink,
to_char(r.revealed, 'YYYY-MM-DD') as revealed,
r.classes
from
makes use of u
be part of
rss_item r
on
r.feed_link = u.feed_link
the place
recent_uses > 1
order by
recent_uses desc, revealed desc
)
The brand new substances, courtesy of the RSS feed, are guidwhich hyperlinks to a solo introduction like Kathy’s; revealed, which is the day the introduction appeared; Y classes, which has the tags used within the intro submit. Candy! Now I can scan the board to get an concept of what displays I need to see.
The primary three queries use the Steampipe plugin for Mastodon, and specifically its mastodon_search_hashtag desk , which encapsulates the Mastodon API for looking for tags. The ultimate model joins that desk with the rss_item desk supplied by the RSS plugin, utilizing the widespread base URL as the idea for the be part of.
This delights me in some ways. When the blogosphere first emerged within the early 2000s, a few of us found that the RSS protocol was able to way more than simply delivering feeds to RSS readers. The opposite sizzling new protocol on the time was XML internet companies. As an InfoWorld analyst, I used to be purported to be rooting for the latter as an enterprise-grade expertise, however I could not assist however discover that RSS saved turning out to be a good way to maneuver information between cooperative programs. That is all the time been true, and I really like how this instance reminds us that it is nonetheless true.
I am equally delighted to indicate how Steampipe allows this contemporary train in RSS-based integration. Steampipe was initially an engine for mapping outcomes from JSON API endpoints to SQL tables. Nonetheless, over time, you will have broadened your view of what constitutes an API. You need to use Steampipe to question CSV recordsdata or Terraform recordsdata or, as we see right here, RSS feeds. Knowledge is available in all kinds of flavors. Steampipe abstracts away these variations and brings all of the flavors into a standard area the place you may purpose about them utilizing SQL.
And eventually, it is simply fantastic to be on the intersection of Mastodon, Steampipe and RSS at this extraordinary time. I will readily admit that nostalgia is an element. However RSS absolutely opened issues up 20 years in the past, Mastodon is doing it now, and I really like that RSS might help make it occur once more.
Now I want to jot down that #intro!
