Skip to content

Instantly share code, notes, and snippets.

@sspaeti
Created October 30, 2024 09:13
Show Gist options
  • Save sspaeti/31d5c29a8f794aa3172fef229f2ad46e to your computer and use it in GitHub Desktop.
Save sspaeti/31d5c29a8f794aa3172fef229f2ad46e to your computer and use it in GitHub Desktop.
Reading bsky posts with DuckDB example.
-- Query the API directly and flatten the nested JSON structure
WITH raw_data AS (
SELECT * FROM read_json_auto('https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed?actor=did:plc:edglm4muiyzty2snc55ysuqx&limit=10')
),
unnested_feed AS (
SELECT unnest(feed) as post_data FROM raw_data
)
SELECT
-- Post basics
post_data.post.uri as post_uri,
post_data.post.author.handle as author_handle,
post_data.post.author.displayName as display_name,
-- Post content
post_data.post.record.text as post_text,
post_data.post.record.createdAt as created_at,
-- Engagement metrics
post_data.post.replyCount as replies,
post_data.post.repostCount as reposts,
post_data.post.likeCount as likes,
post_data.post.quoteCount as quotes,
-- Embedded content (if available)
CASE
WHEN post_data.post.embed IS NOT NULL
AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view'
THEN post_data.post.embed.external.uri
ELSE NULL
END as embedded_link,
CASE
WHEN post_data.post.embed IS NOT NULL
AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view'
THEN post_data.post.embed.external.title
ELSE NULL
END as embedded_title,
-- Total engagement score
(post_data.post.replyCount +
post_data.post.repostCount +
post_data.post.likeCount +
post_data.post.quoteCount) as total_engagement
FROM unnested_feed
ORDER BY created_at DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment