< img src="https://images.idgesg.net/images/article/2018/10/abstract-arrows_direction_process_magnifying-glass_search_investigate-100777420-large.jpg?auto=webp&quality=85,70"alt= "" > I was figured out to write my Mastodon #introduction today. To begin I used the tag search in the control panel I’m developing.
IDG The idea was to look at a lot of other #introduction posts to get a feel for how mine should go. When you browse specifically for hashtags, the Mastodon search API returns this information. “hashtags”: [“name”:”introduction”,”url”:”https:, A first variation of the dashboard, having only this information to deal with, just listed the names of tags matching the search term in addition to matching URLs. Here was the preliminary query.select name, url from mastodon_search_hashtag where inquiry=’intro ‘That produced a list of links, like https://mastodon.social/tags/introduction, to web page 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 a little better, I tapped the 3rd aspect of the API action, history, in a modified query.with data as (select name, url, (jsonb_array_elements(history) ->> ‘uses’ ):: int as usages from mastodon_search_hashtag where question=’introduction’) select name, url, amount(uses) from data group by name, url order by sum desc
These outcomes assist me choose which variation to utilize.
+——————-+—————————————————+——+|name|url|sum|+——————-+—————————————————+——+|introduction|https://mastodon.social/tags/introduction|1816|| intros|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 good to surface more context in the dashboard, and I found an awesome method to do it, but first let’s dwell on the modified query for a minute. Postgres’s JSON includes are effective and it’s often a challenge (at least for me) to imagine how they work.The Postgres jsonb_array_elements()function is what’s called a set-returning function. Here it unloads Postgres’s JSON representation of the list of history structures returned from the Mastodon API. In its most basic kind, the function call jsonb_array_elements(history) produces a short-term table with per-tag, per-day information.
choose name, jsonb_array_elements(history) as history from mastodon_search_hashtag where question=’intro’ +——————————–+—————————————————-+|name|history|+——————————–+—————————————————-+|introduction||| intro|”accounts”:”250″,”day”:”1670284800″,”usages”:”269″|| introduction||| intro||| intro|”accounts”:”245″,”day”:”1670025600″,”uses”:”269″|| intro|”accounts”:”345″,”day”:”1669939200″,”usages”:”383″|| intro|”accounts”:”307″,”day”:”1669852800″,”uses”:”339″|| introductionsfr||| introductionsfr||| introductionsfr|”accounts”:”0″,”day”:”1670198400″,”uses”:”0″|| introductionsfr||| introductionsfr||
history is a JSONB column that holds an item with three fields. The modified question uses Postgres’s JSON indexing operator ->> to reach into that item and raise the variety of day-to-day uses into its own column, so it can be the target of a SQL amount function.OK, all set for
the cool solution? Recall that https://mastodon.social/tags/introduction is the web page for that variant of the tag. There you can see introduction posts from individuals using the tag. Those posts typically include other tags. In the control panel revealed above you can see that Kathy Nickels is using these: #Music #Art #Equestrian #Nature #Animals. The tags appear in her intro post.
IDG I didn’t instantly see how to catch them for usage in the dashboard. Then I bore in mind that particular classes of Mastodon page have matching RSS feeds, and questioned 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, offers the extra context I was looking for. Here’s the last variation of the query.with information as (choose name, url, (jsonb_array_elements(history) ->> ‘usages’ ):: int as usages from mastodon_search_hashtag where question=’introduction’ ), utilizes as (select name, url|| ‘. rss’ as feed_link, sum(uses) as recent_uses from data group by connection, name, url) choose u.name, r.guid as link, to_char(r.published, ‘YYYY-MM-DD’) as published, r.categories from usages u sign up with rss_item r on r.feed _ link = u.feed _ link where recent_uses > 1 order by recent_uses desc, released desc)
The new active ingredients, thanks to the RSS feed, are guid, which connects to an individual intro like Kathy’s; published, which is the day the intro appeared; and classifications, which has actually the tags utilized in the introduction post. Sweet! Now I can scan the control panel to get a sense of which introductions I wish to take a look at.
The very first three queries utilize the Steampipe plugin for Mastodon, and in specific its mastodon_search_hashtag table, which encapsulates the Mastodon API for browsing tags. The final variation joins that table with the rss_item table supplied by the RSS plugin, using the common base URL as the basis of the join.This delights me in numerous methods. When the blogosphere first emerged in the early 2000s, a few of us discovered that the RSS protocol was capable of even more than just delivering feeds to RSS readers. The other brand-new hot procedure because age was XML web services. As an InfoWorld expert I was supposed to be cheering the latter as an enterprise-grade innovation, but I couldn’t assist noticing that RSS kept turning out to be a great way to move information in between working together systems. That’s constantly held true, and I like how this example reminds us that it’s still true.I’m equally thrilled to show how Steampipe enables this modern-day exercise in RSS-powered integration. Steampipe was, originally, an engine for mapping results from JSON API endpoints to SQL tables. In time, however, it has actually 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 is available in all kinds of flavors. Steampipe abstracts those distinctions and brings all the flavors into a common space where you can reason over them utilizing SQL.And finally, it’s simply terrific to be at the crossway of Mastodon, Steampipe, and RSS in this impressive moment. I’ll readily confess that nostalgia is a factor. But RSS did bust things wide open 20 years back, Mastodon’s doing that now, and I enjoy that RSS can assist it take place again.
Now I require to compose that #introduction! Copyright © 2022 IDG Communications, Inc. Source
