Expose errors in .json mode

[simonw]

trafficstars

https://simonwillison.net/dashboard/tag-cloud-by-year.json?year=2023 returns this:

{
  "title": "Tag cloud by year",
  "queries": [
    {
      "sql": "SELECT \"tag\" AS wordcloud_word, COUNT(*) AS wordcloud_count \r\nFROM (\r\n    SELECT blog_tag.tag \r\n    FROM blog_entry_tags \r\n    JOIN blog_tag ON blog_entry_tags.tag_id = blog_tag.id\r\n    JOIN blog_entry ON blog_entry_tags.entry_id = blog_entry.id\r\n    WHERE EXTRACT(YEAR FROM blog_entry.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_blogmark_tags \r\n    JOIN blog_tag ON blog_blogmark_tags.tag_id = blog_tag.id\r\n    JOIN blog_blogmark ON blog_blogmark_tags.blogmark_id = blog_blogmark.id\r\n    WHERE EXTRACT(YEAR FROM blog_blogmark.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_quotation_tags \r\n    JOIN blog_tag ON blog_quotation_tags.tag_id = blog_tag.id\r\n    JOIN blog_quotation ON blog_quotation_tags.quotation_id = blog_quotation.id\r\n    WHERE EXTRACT(YEAR FROM blog_quotation.created) = %(year)s\r\n) AS results \r\nWHERE tag != 'quora' \r\nGROUP BY \"tag\" \r\nORDER BY wordcloud_count DESC",
      "rows": [
        {
          "wordcloud_word": "ai",
          "wordcloud_count": 342
        },

But https://simonwillison.net/dashboard/tag-cloud-by-year.json returns this:

{
  "title": "Tag cloud by year",
  "queries": [
    {
      "sql": "SELECT \"tag\" AS wordcloud_word, COUNT(*) AS wordcloud_count \r\nFROM (\r\n    SELECT blog_tag.tag \r\n    FROM blog_entry_tags \r\n    JOIN blog_tag ON blog_entry_tags.tag_id = blog_tag.id\r\n    JOIN blog_entry ON blog_entry_tags.entry_id = blog_entry.id\r\n    WHERE EXTRACT(YEAR FROM blog_entry.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_blogmark_tags \r\n    JOIN blog_tag ON blog_blogmark_tags.tag_id = blog_tag.id\r\n    JOIN blog_blogmark ON blog_blogmark_tags.blogmark_id = blog_blogmark.id\r\n    WHERE EXTRACT(YEAR FROM blog_blogmark.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_quotation_tags \r\n    JOIN blog_tag ON blog_quotation_tags.tag_id = blog_tag.id\r\n    JOIN blog_quotation ON blog_quotation_tags.quotation_id = blog_quotation.id\r\n    WHERE EXTRACT(YEAR FROM blog_quotation.created) = %(year)s\r\n) AS results \r\nWHERE tag != 'quora' \r\nGROUP BY \"tag\" \r\nORDER BY wordcloud_count DESC",
      "rows": []
    }
  ]
}

Actually if you visit https://simonwillison.net/dashboard/tag-cloud-by-year/ you see an error:

[simonw]

Note that it's possible for a page to have some queries that work and others that fail:

So the errors should be in the queries block, maybe like this:

{
  "title": "Tag cloud by year",
  "queries": [
    {
      "sql": "SELECT \"tag\" AS wordcloud_word, COUNT(*) AS wordcloud_count \r\nFROM (\r\n    SELECT blog_tag.tag \r\n    FROM blog_entry_tags \r\n    JOIN blog_tag ON blog_entry_tags.tag_id = blog_tag.id\r\n    JOIN blog_entry ON blog_entry_tags.entry_id = blog_entry.id\r\n    WHERE EXTRACT(YEAR FROM blog_entry.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_blogmark_tags \r\n    JOIN blog_tag ON blog_blogmark_tags.tag_id = blog_tag.id\r\n    JOIN blog_blogmark ON blog_blogmark_tags.blogmark_id = blog_blogmark.id\r\n    WHERE EXTRACT(YEAR FROM blog_blogmark.created) = %(year)s\r\nUNION ALL\r\n    SELECT blog_tag.tag \r\n    FROM blog_quotation_tags \r\n    JOIN blog_tag ON blog_quotation_tags.tag_id = blog_tag.id\r\n    JOIN blog_quotation ON blog_quotation_tags.quotation_id = blog_quotation.id\r\n    WHERE EXTRACT(YEAR FROM blog_quotation.created) = %(year)s\r\n) AS results \r\nWHERE tag != 'quora' \r\nGROUP BY \"tag\" \r\nORDER BY wordcloud_count DESC",
      "rows": []
      "error": "error here"
    }
  ]
}

With "error": null for queries that work.

[simonw]

I think I'll add a "ok": true/false key as well.