Union JSON demo

Owned by simonw, visibility: Unlisted

SQL query
WITH blogmarks AS (
    SELECT 
        bb.id,
        bb.slug,
        bb.link_url,
        bb.link_title,
        bb.created,
        COALESCE(
            json_agg(DISTINCT bt.tag) FILTER (WHERE bt.tag IS NOT NULL),
            '[]'::json
        ) AS tags
    FROM 
        blog_blogmark bb
    LEFT JOIN 
        blog_blogmark_tags bbt ON bb.id = bbt.blogmark_id
    LEFT JOIN 
        blog_tag bt ON bbt.tag_id = bt.id
    GROUP BY 
        bb.id, bb.slug, bb.link_url, bb.link_title, bb.created
    ORDER BY 
        bb.created DESC
    LIMIT 20
),
entries AS (
    SELECT 
        be.id,
        be.title,
        be.slug,
        be.created,
        COALESCE(
            json_agg(DISTINCT bt.tag) FILTER (WHERE bt.tag IS NOT NULL),
            '[]'::json
        ) AS tags
    FROM 
        blog_entry be
    LEFT JOIN 
        blog_entry_tags bet ON be.id = bet.entry_id
    LEFT JOIN 
        blog_tag bt ON bet.tag_id = bt.id
    GROUP BY 
        be.id, be.title, be.slug, be.created
    ORDER BY 
        be.created DESC
    LIMIT 10
)
SELECT 
    'blogmarks' AS type,
    json_agg(
        json_build_object(
            'id', id,
            'slug', slug,
            'link_url', link_url,
            'link_title', link_title,
            'created', created,
            'tags', tags
        )
    ) AS rows
FROM 
    blogmarks

UNION ALL

SELECT 
    'entries' AS type,
    json_agg(
        json_build_object(
            'id', id,
            'title', title,
            'slug', slug,
            'created', created,
            'tags', tags
        )
    ) AS rows
FROM 
    entries

2 rows

type rows
blogmarks
[
  {
    "id": 9207,
    "slug": "uv-init-demos",
    "link_url": "https://github.com/simonw/uv-init-demos",
    "link_title": "uv-init-demos",
    "created": "2025-12-24T22:05:23+00:00",
    "tags": [
      "github-actions",
      "git-scraping",
      "projects",
      "python",
      "uv"
    ]
  },
  {
    "id": 9206,
    "slug": "microquickjs",
    "link_url": "https://github.com/bellard/mquickjs",
    "link_title": "MicroQuickJS",
    "created": "2025-12-23T20:53:40+00:00",
    "tags": [
      "ai",
      "c",
      "claude-code",
      "deno",
      "fabrice-bellard",
      "generative-ai",
      "javascript",
      "llms",
      "nodejs",
      "pyodide",
      "python",
      "sandboxing",
      "webassembly"
    ]
  },
  {
    "id": 9205,
    "slug": "sam-rose-llms",
    "link_url": "https://ngrok.com/blog/prompt-caching/",
    "link_title": "Sam Rose explains how LLMs work with a visual essay",
    "created": "2025-12-19T18:33:41+00:00",
    "tags": [
      "ai",
      "explorables",
      "generative-ai",
      "llms",
      "sam-rose",
      "tokenization"
    ]
  },
  {
    "id": 9204,
    "slug": "introducing-gpt-52-codex",
    "link_url": "https://openai.com/index/introducing-gpt-5-2-codex/",
    "link_title": "Introducing GPT-5.2-Codex",
    "created": "2025-12-19T05:21:17+00:00",
    "tags": [
      "ai",
      "codex-cli",
      "generative-ai",
      "gpt-codex",
      "llm-release",
      "llms",
      "openai",
      "pelican-riding-a-bicycle"
    ]
  },
  {
    "id": 9203,
    "slug": "agent-skills",
    "link_url": "https://agentskills.io/",
    "link_title": "Agent Skills",
    "created": "2025-12-19T01:09:18+00:00",
    "tags": [
      "ai",
      "ai-agents",
      "anthropic",
      "coding-agents",
      "generative-ai",
      "llms",
      "skills"
    ]
  },
  {
    "id": 9202,
    "slug": "swift-justhtml",
    "link_url": "https://github.com/kylehowells/swift-justhtml",
    "link_title": "swift-justhtml",
    "created": "2025-12-18T23:57:58+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "generative-ai",
      "html5",
      "llms",
      "swift",
      "vibe-coding"
    ]
  },
  {
    "id": 9201,
    "slug": "ssrf-clickhouse-postgresql",
    "link_url": "https://mdisec.com/inside-posthog-how-ssrf-a-clickhouse-sql-escaping-0day-and-default-postgresql-credentials-formed-an-rce-chain-zdi-25-099-zdi-25-097-zdi-25-096/",
    "link_title": "Inside PostHog: How SSRF, a ClickHouse SQL Escaping 0day, and Default PostgreSQL Credentials Formed an RCE Chain",
    "created": "2025-12-18T01:42:22+00:00",
    "tags": [
      "clickhouse",
      "postgresql",
      "security",
      "webhooks"
    ]
  },
  {
    "id": 9200,
    "slug": "vibespiling",
    "link_url": "https://anil.recoil.org/notes/aoah-2025-15",
    "link_title": "AoAH Day 15: Porting a complete HTML5 parser and browser test suite",
    "created": "2025-12-17T23:23:35+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "ai-ethics",
      "definitions",
      "functional-programming",
      "generative-ai",
      "llms",
      "ocaml",
      "vibe-coding"
    ]
  },
  {
    "id": 9198,
    "slug": "firefox-parser",
    "link_url": "https://github.com/mozilla-firefox/firefox/tree/main/parser/html/java",
    "link_title": "firefox parser/html/java/README.txt",
    "created": "2025-12-17T01:48:54+00:00",
    "tags": [
      "c-plus-plus",
      "firefox2",
      "henri-sivonen",
      "java",
      "john-resig",
      "mozilla"
    ]
  },
  {
    "id": 9197,
    "slug": "new-chatgpt-images",
    "link_url": "https://openai.com/index/new-chatgpt-images-is-here/",
    "link_title": "The new ChatGPT Images is here",
    "created": "2025-12-16T23:59:22+00:00",
    "tags": [
      "ai",
      "generative-ai",
      "kakapo",
      "nano-banana",
      "openai",
      "text-to-image"
    ]
  },
  {
    "id": 9196,
    "slug": "s3-credentials",
    "link_url": "https://github.com/simonw/s3-credentials/releases/tag/0.17",
    "link_title": "s3-credentials 0.17",
    "created": "2025-12-16T23:40:31+00:00",
    "tags": [
      "ai",
      "annotated-release-notes",
      "aws",
      "claude-code",
      "coding-agents",
      "generative-ai",
      "llms",
      "projects",
      "prompt-engineering",
      "s3",
      "s3-credentials"
    ]
  },
  {
    "id": 9195,
    "slug": "ty",
    "link_url": "https://astral.sh/blog/ty",
    "link_title": "ty: An extremely fast Python type checker and LSP",
    "created": "2025-12-16T23:35:33+00:00",
    "tags": [
      "astral",
      "python",
      "vs-code"
    ]
  },
  {
    "id": 9194,
    "slug": "poe-the-poet",
    "link_url": "https://poethepoet.natn.io/",
    "link_title": "Poe the Poet",
    "created": "2025-12-16T22:57:02+00:00",
    "tags": [
      "packaging",
      "python",
      "s3-credentials",
      "uv"
    ]
  },
  {
    "id": 9193,
    "slug": "2025-word-of-the-year-slop",
    "link_url": "https://www.merriam-webster.com/wordplay/word-of-the-year",
    "link_title": "2025 Word of the Year: Slop",
    "created": "2025-12-15T17:27:59+00:00",
    "tags": [
      "ai",
      "ai-ethics",
      "definitions",
      "generative-ai",
      "slop"
    ]
  },
  {
    "id": 9192,
    "slug": "copywriters-reveal-how-ai-has-decimated-their-industry",
    "link_url": "https://www.bloodinthemachine.com/p/i-was-forced-to-use-ai-until-the",
    "link_title": "Copywriters reveal how AI has decimated their industry",
    "created": "2025-12-14T05:06:19+00:00",
    "tags": [
      "ai",
      "ai-ethics",
      "careers",
      "copywriting"
    ]
  },
  {
    "id": 9191,
    "slug": "llm-028",
    "link_url": "https://llm.datasette.io/en/stable/changelog.html#v0-28",
    "link_title": "LLM 0.28",
    "created": "2025-12-12T20:20:14+00:00",
    "tags": [
      "ai",
      "annotated-release-notes",
      "generative-ai",
      "llm",
      "llms",
      "projects",
      "python",
      "uv"
    ]
  },
  {
    "id": 9190,
    "slug": "normalization-of-deviance",
    "link_url": "https://embracethered.com/blog/posts/2025/the-normalization-of-deviance-in-ai/",
    "link_title": "The Normalization of Deviance in AI",
    "created": "2025-12-10T20:18:58+00:00",
    "tags": [
      "ai",
      "ai-ethics",
      "generative-ai",
      "johann-rehberger",
      "llms",
      "prompt-injection",
      "security"
    ]
  },
  {
    "id": 9189,
    "slug": "lets-encrypt",
    "link_url": "https://letsencrypt.org/2025/12/09/10-years",
    "link_title": "10 Years of Let's Encrypt",
    "created": "2025-12-10T00:34:15+00:00",
    "tags": [
      "https",
      "security"
    ]
  },
  {
    "id": 9188,
    "slug": "devstral-2",
    "link_url": "https://mistral.ai/news/devstral-2-vibe-cli",
    "link_title": "Devstral 2",
    "created": "2025-12-09T23:58:27+00:00",
    "tags": [
      "ai",
      "generative-ai",
      "janky-licenses",
      "llm",
      "llm-release",
      "llms",
      "mistral",
      "pelican-riding-a-bicycle"
    ]
  },
  {
    "id": 9187,
    "slug": "agentic-ai-foundation",
    "link_url": "https://aaif.io/",
    "link_title": "Agentic AI Foundation",
    "created": "2025-12-09T22:24:48+00:00",
    "tags": [
      "ai",
      "ai-agents",
      "anthropic",
      "llms",
      "model-context-protocol",
      "openai",
      "open-source",
      "standards"
    ]
  }
]
entries
[
  {
    "id": 9106,
    "title": "A new way to extract detailed transcripts from Claude Code",
    "slug": "claude-code-transcripts",
    "created": "2025-12-25T23:52:17+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "anthropic",
      "claude",
      "claude-code",
      "coding-agents",
      "generative-ai",
      "llms",
      "projects"
    ]
  },
  {
    "id": 9096,
    "title": "Cooking with Claude",
    "slug": "cooking-with-claude",
    "created": "2025-12-23T05:01:34+00:00",
    "tags": [
      "ai",
      "anthropic",
      "claude",
      "cooking",
      "devfort",
      "generative-ai",
      "llms",
      "tools",
      "vibe-coding",
      "vision-llms"
    ]
  },
  {
    "id": 9095,
    "title": "Your job is to deliver code you have proven to work",
    "slug": "code-proven-to-work",
    "created": "2025-12-18T14:49:38+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "ai-ethics",
      "careers",
      "coding-agents",
      "generative-ai",
      "llms",
      "programming",
      "vibe-coding"
    ]
  },
  {
    "id": 9094,
    "title": "Gemini 3 Flash",
    "slug": "gemini-3-flash",
    "created": "2025-12-17T22:44:52+00:00",
    "tags": [
      "ai",
      "gemini",
      "generative-ai",
      "google",
      "llm",
      "llm-pricing",
      "llm-release",
      "llms",
      "pelican-riding-a-bicycle",
      "web-components"
    ]
  },
  {
    "id": 9093,
    "title": "I ported JustHTML from Python to JavaScript with Codex CLI and GPT-5.2 in 4.5 hours",
    "slug": "porting-justhtml",
    "created": "2025-12-15T23:58:38+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "codex-cli",
      "generative-ai",
      "gpt-5",
      "html",
      "javascript",
      "llms",
      "python"
    ]
  },
  {
    "id": 9092,
    "title": "JustHTML is a fascinating example of vibe engineering in action",
    "slug": "justhtml",
    "created": "2025-12-14T15:59:23+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "coding-agents",
      "generative-ai",
      "html",
      "llms",
      "python",
      "vibe-coding"
    ]
  },
  {
    "id": 9091,
    "title": "OpenAI are quietly adopting skills, now available in ChatGPT and Codex CLI",
    "slug": "openai-skills",
    "created": "2025-12-12T23:29:51+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "anthropic",
      "chatgpt",
      "codex-cli",
      "coding-agents",
      "generative-ai",
      "gpt-5",
      "kakapo",
      "llms",
      "openai",
      "pdf",
      "prompt-engineering",
      "skills"
    ]
  },
  {
    "id": 9090,
    "title": "GPT-5.2",
    "slug": "gpt-52",
    "created": "2025-12-11T23:58:04+00:00",
    "tags": [
      "ai",
      "generative-ai",
      "gpt-5",
      "llm",
      "llm-release",
      "llms",
      "openai",
      "pelican-riding-a-bicycle"
    ]
  },
  {
    "id": 9089,
    "title": "Useful patterns for building HTML tools",
    "slug": "html-tools",
    "created": "2025-12-10T21:00:59+00:00",
    "tags": [
      "ai",
      "ai-assisted-programming",
      "claude-code",
      "coding-agents",
      "definitions",
      "generative-ai",
      "github",
      "html",
      "javascript",
      "llms",
      "projects",
      "tools",
      "vibe-coding",
      "webassembly"
    ]
  },
  {
    "id": 9088,
    "title": "Under the hood of Canada Spends with Brendan Samek",
    "slug": "canada-spends",
    "created": "2025-12-09T23:52:05+00:00",
    "tags": [
      "data-journalism",
      "datasette",
      "politics",
      "sqlite",
      "sqlite-utils",
      "youtube"
    ]
  }
]
Copy and export data

Duration: 43.06ms