• Home
  • Insight
  • Blog
  • Business
  • Entertainment
  • Health
  • Politics
  • Shop
    • Gift Shop
    • Value Shop
    • Store
    • Bargain Shop
    • Discount
  • Sports
  • Tech
  • Travel
  • USA
  • Video
  • World
    • Asia
    • Africa
    • South America
    • North America
    • Europe
    • Oceania
Thursday, November 19, 2026
No Result
View All Result
Subscribe Now
  • Home
  • Insight
  • Blog
  • Business
  • Entertainment
  • Health
  • Politics
  • Shop
    • Gift Shop
    • Value Shop
    • Store
    • Bargain Shop
    • Discount
  • Sports
  • Tech
  • Travel
  • USA
    Trump’s Deal Turns Sour as Voters Withdraw Support

    Trump’s Deal Turns Sour as Voters Withdraw Support

    FAA Administrator Takes Responsibility as Mullin Faces Tough Questions Over Nationwide Ground Stop

    FAA Administrator Takes Responsibility as Mullin Faces Tough Questions Over Nationwide Ground Stop

    Mary Peltola Emerges Victorious in Alaska Senate Race, Making History for Democrats

    Mary Peltola Emerges Victorious in Alaska Senate Race, Making History for Democrats

    Stranded in Conflict: Palestinians Navigate the Tenuous Balance of War and Peace Amid Ceasefire Efforts

    Stranded in Conflict: Palestinians Navigate the Tenuous Balance of War and Peace Amid Ceasefire Efforts

    Trump Administration Revokes Pay Increase for Head Start Educators

    Trump Administration Revokes Pay Increase for Head Start Educators

    Unexpected Triumph: Gay Candidates Achieve Unforeseen Victories in Challenging Races

    Unexpected Triumph: Gay Candidates Achieve Unforeseen Victories in Challenging Races

    Microsoft Outlook Experiences Prolonged Outage Affecting Users Nationwide

    Microsoft Outlook Experiences Prolonged Outage Affecting Users Nationwide

    Gunfire Erupts in D.C.: Authorities Search for Suspect Amid Rising Concerns Over Public Safety

    Gunfire Erupts in D.C.: Authorities Search for Suspect Amid Rising Concerns Over Public Safety

    LAUSD Takes Strong Action Against Hate Speech Following Racial Slur Incidents

    LAUSD Takes Strong Action Against Hate Speech Following Racial Slur Incidents

  • Video
  • World
    • Asia
    • Africa
    • South America
    • North America
    • Europe
    • Oceania
The Insight Post
  • Home
  • Insight
  • Blog
  • Business
  • Entertainment
  • Health
  • Politics
  • Shop
    • Gift Shop
    • Value Shop
    • Store
    • Bargain Shop
    • Discount
  • Sports
  • Tech
  • Travel
  • USA
    Trump’s Deal Turns Sour as Voters Withdraw Support

    Trump’s Deal Turns Sour as Voters Withdraw Support

    FAA Administrator Takes Responsibility as Mullin Faces Tough Questions Over Nationwide Ground Stop

    FAA Administrator Takes Responsibility as Mullin Faces Tough Questions Over Nationwide Ground Stop

    Mary Peltola Emerges Victorious in Alaska Senate Race, Making History for Democrats

    Mary Peltola Emerges Victorious in Alaska Senate Race, Making History for Democrats

    Stranded in Conflict: Palestinians Navigate the Tenuous Balance of War and Peace Amid Ceasefire Efforts

    Stranded in Conflict: Palestinians Navigate the Tenuous Balance of War and Peace Amid Ceasefire Efforts

    Trump Administration Revokes Pay Increase for Head Start Educators

    Trump Administration Revokes Pay Increase for Head Start Educators

    Unexpected Triumph: Gay Candidates Achieve Unforeseen Victories in Challenging Races

    Unexpected Triumph: Gay Candidates Achieve Unforeseen Victories in Challenging Races

    Microsoft Outlook Experiences Prolonged Outage Affecting Users Nationwide

    Microsoft Outlook Experiences Prolonged Outage Affecting Users Nationwide

    Gunfire Erupts in D.C.: Authorities Search for Suspect Amid Rising Concerns Over Public Safety

    Gunfire Erupts in D.C.: Authorities Search for Suspect Amid Rising Concerns Over Public Safety

    LAUSD Takes Strong Action Against Hate Speech Following Racial Slur Incidents

    LAUSD Takes Strong Action Against Hate Speech Following Racial Slur Incidents

  • Video
  • World
    • Asia
    • Africa
    • South America
    • North America
    • Europe
    • Oceania
No Result
View All Result
No Result
View All Result
Home Tech

Mastodon, Steampipe, and RSS | InfoWorld

by Theinsightpost
December 30, 2022
in Tech
0 0
0
Mastodon, Steampipe, and RSS | InfoWorld


I was determined to write my Mastodon #introduction today. To get started I used the tag search in the dashboard I’m building.

mastodon tag search kathy nickels in the mod3 IDG

The 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.

mastodon tag search kathy nickels in the app IDG

I 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!

Copyright © 2022 IDG Communications, Inc.



Source link

ShareTweetSend
Previous Post

Ukraine Tried to Coerce Danish Reporter to Make Propaganda

Next Post

Killian Hayes, Mo Wagner part of massive 11-player suspensions

Related News

Microsoft’s Satya Nadella Reveals Strategic Rationale Behind Layoff of 60,000 Employees
Tech

Microsoft’s Satya Nadella Reveals Strategic Rationale Behind Layoff of 60,000 Employees

November 19, 2026
Cybercriminals Embrace Innovative AI Tool for Streamlined Hacking Operations
Tech

Cybercriminals Embrace Innovative AI Tool for Streamlined Hacking Operations

November 18, 2026
Transform Your Laptop Setup with These 5 Affordable Accessories
Tech

Transform Your Laptop Setup with These 5 Affordable Accessories

November 15, 2026
Microsoft 365 Services Gradually Restore Functionality Following Global Outage
Tech

Microsoft 365 Services Gradually Restore Functionality Following Global Outage

November 14, 2026
Next Post
Killian Hayes, Mo Wagner part of massive 11-player suspensions

Killian Hayes, Mo Wagner part of massive 11-player suspensions

Discussion about this post

Subscribe To Our Newsletters

    Customer Support


    1251 Wilcrest Drive
    Houston, Texas
    77042 USA
    Call-832.795.1420
    e-mail – news@theinsightpost.com

    Subscribe To Our Newsletters

      Categories

      • Africa
      • Africa-East
      • African Sports
      • American Sports
      • Arts
      • Asia
      • Australia
      • Business
      • Business Asia
      • Business- Africa
      • Canada
      • Defense
      • Education
      • Egypt
      • Energy
      • Entertainment
      • Europe
      • European Soccer
      • Finance
      • Germany
      • Ghana
      • Health
      • Insight
      • International
      • Investing
      • Japan
      • Latest Headlines
      • Life & Living
      • Markets
      • Mobile
      • Movies
      • New Zealand
      • Nigeria
      • Politics
      • Scholarships
      • Science
      • South Africa
      • South America
      • Sports
      • Tech
      • Travel
      • Travel-Africa
      • UK
      • USA
      • Weather
      • World
      No Result
      View All Result

      Recent News

      APC Leader Calls for Unity Among Tinubu and Yari to Strengthen Kano’s Political Landscape in 2027

      APC Leader Calls for Unity Among Tinubu and Yari to Strengthen Kano’s Political Landscape in 2027

      November 19, 2026
      Trump’s Deal Turns Sour as Voters Withdraw Support

      Trump’s Deal Turns Sour as Voters Withdraw Support

      November 19, 2026
      Hong Kong’s Building Maintenance Authority Secures HK3 Million to Expand Operations and Recruit 160 New Employees

      Hong Kong’s Building Maintenance Authority Secures HK$413 Million to Expand Operations and Recruit 160 New Employees

      November 19, 2026
      Authorities Respond to Firearms Incident in West Melton, Canterbury

      Authorities Respond to Firearms Incident in West Melton, Canterbury

      November 19, 2026
      • Home
      • Advertise With Us
      • About Us
      • Corporate
      • Consumer Rewards
      • Forum
      • Privacy Policy
      • Social Trends

      Theinsightpost ©2026 | All Rights Reserved. Theinsightpost is an Elnegy LLC company, registered in Texas, USA

      Welcome Back!

      Login to your account below

      Forgotten Password?

      Retrieve your password

      Please enter your username or email address to reset your password.

      Log In

      Add New Playlist

      We are using cookies to give you the best experience on our website.

      You can find out more about which cookies we are using or switch them off in .

      No Result
      View All Result
      • Home
      • Insight
      • Blog
      • Business
      • Entertainment
      • Health
      • Politics
      • Shop
        • Gift Shop
        • Value Shop
        • Store
        • Bargain Shop
        • Discount
      • Sports
      • Tech
      • Travel
      • USA
      • Video
      • World
        • Asia
        • Africa
        • South America
        • North America
        • Europe
        • Oceania

      Theinsightpost ©2026 | All Rights Reserved. Theinsightpost is an Elnegy LLC company, registered in Texas, USA

      The Insight Post
      Powered by  GDPR Cookie Compliance
      Privacy Overview

      This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

      Strictly Necessary Cookies

      Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

      Cookie Policy

      More information about our Cookie Policy