Avoiding {"tag": null} in results from a json_group_array() aggregation

(1.1) By Simon Willison (simonw) on 2022-09-16 20:04:22 edited from 1.0 [link] [source]

I wrote an article about how to use json_group_array() to return related rows (e.g. tags from a many-to-many relationship) in a single SQL query:

https://til.simonwillison.net/sqlite/related-rows-single-query

There's one open question for me there though: given the following query, how can I avoid returning [{"tag":null}] and instead return [] for blog entries that have no tags?

select
  blog_entry.id,
  blog_entry.title,
  json_group_array(json_object('tag', blog_tag.tag)) as tags
from
  blog_entry
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
  left join blog_tag on blog_tag.id = blog_entry_tags.tag_id
group by
  blog_entry.id
order by
  blog_entry.id

Here's the result of that query - entries that do not have any tags are represented like this:

1 - WaSP Phase II - [{"tag":null}]

Is there a more elegant solution for this problem than adding replace(tags, '[{"tag":null}]', '[]') to the query (like this)?

(2.1) By Simon Willison (simonw) on 2022-09-16 21:29:13 edited from 2.0 in reply to 1.1 [source]

Solution from David Fetter on Twitter:

select
  blog_entry.id,
  blog_entry.title,
  json_group_array(
    json_object('tag', blog_tag.tag) 
  ) filter (
    where
      blog_tag.tag is not null
  ) as tags
from
  blog_entry
  left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
  left join blog_tag on blog_tag.id = blog_entry_tags.tag_id
group by
  blog_entry.id
order by
  blog_entry.id

Demonstrated here.