Table: mastodon_notification - Query Mastodon Notifications using SQL

Mastodon is a decentralized, open-source social network. Notifications in Mastodon represent a user's interactions and activities on the platform, such as mentions, follows, and likes. It provides users with updates on their interactions, activities, and the latest posts from the accounts they follow.

Table Usage Guide

The mastodon_notification table provides insights into user interactions and activities on the Mastodon platform. As a social media manager or a digital marketer, explore detailed notifications through this table, including mentions, follows, and likes. Utilize it to uncover information about user activity and engagement, such as who mentioned whom, who followed whom, and which posts are liked.

Examples

Recent notifications

Discover the most recent notifications on your Mastodon account, allowing you to stay updated on recent activities and interactions. This is beneficial for managing your social media presence and responding to any required actions promptly.

select
  category,
  created_at,
  account ->> 'acct' as account
from
  mastodon_notification
limit
  20;

select
  category,
  created_at,
  json_extract(account, '$.acct') as account
from
  mastodon_notification
limit
  20;

Count notifications by category

Determine the areas in which different categories of notifications are most prevalent. This can help identify where most user activity is concentrated and guide resource allocation for better user engagement.

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

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

List details of recent notifications

This query allows users to examine the most recent notifications on their Mastodon account. It helps users to stay updated on their social interactions by providing information such as the notification category, the person involved, and the content of the status update.

with notifications as (
  select
    category,
    instance_qualified_account_url,
    account_id,
    display_name as person,
    to_char(created_at, 'MM-DD HH24:MI') as created_at,
    instance_qualified_status_url,
    status_content
  from
    mastodon_notification
  limit
    100
)
select
  n.created_at,
  n.category,
  n.person,
  n.instance_qualified_account_url,
  case
    when r.following then '✔️'
    else ''
  end as following,
  case
    when r.followed_by then '✔️'
    else ''
  end as followed_by,
  substring(
    n.status_content
    from
      1 for 200
  ) as toot,
  case
    when n.instance_qualified_status_url != '' then n.instance_qualified_status_url
    else n.instance_qualified_account_url
  end as url
from
  notifications n
  join mastodon_relationship r on r.id = n.account_id
order by
  n.created_at desc;

with notifications as (
  select
    category,
    instance_qualified_account_url,
    account_id,
    display_name as person,
    strftime('%m-%d %H:%M', created_at) as created_at,
    instance_qualified_status_url,
    status_content
  from
    mastodon_notification
  limit
    100
)
select
  n.created_at,
  n.category,
  n.person,
  n.instance_qualified_account_url,
  case
    when r.following then '✔️'
    else ''
  end as following,
  case
    when r.followed_by then '✔️'
    else ''
  end as followed_by,
  substr(n.status_content, 1, 200) as toot,
  case
    when n.instance_qualified_status_url != '' then n.instance_qualified_status_url
    else n.instance_qualified_account_url
  end as url
from
  notifications n
  join mastodon_relationship r on r.id = n.account_id
order by
  n.created_at desc;

Schema for mastodon_notification

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
account	jsonb		Account of notification sender.
account_id	text		Account ID of notification sender.
account_url	text		Account URL of notification sender.
category	text		Type of notification.
created_at	timestamp with time zone		Timestamp when notification occurred.
display_name	text		Display name of notification sender.
id	text	=	Unique identification for the notification.
instance_qualified_account_url	text		Account URL of notification sender, prefixed with home server.
instance_qualified_status_url	text		Status URL of the notification (if any), prefixed with home server.
status	jsonb		Status (toot) associated with notification (if any).
status_content	text		Status content of the notification (if any).
status_url	text		Status URL of the notification (if any).

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_notification