Null case comparisons in SQLite

I wanted to say "output this transformed value if it's not null, otherwise nothing". The recipe I figured out was:

  case
    when (media_url_https is not null) then json_object('img_src', media_url_https, 'width', 300)
  end as photo

Full query example:

select
  created_at,
  regexp_match('.*?(\d+(\.\d+))lb.*', full_text, 1) as lbs,
  full_text,
  case
    when (media_url_https is not null) then json_object('img_src', media_url_https, 'width', 300)
  end as photo
from
  tweets
  left join media_tweets on tweets.id = media_tweets.tweets_id
  left join media on media.id = media_tweets.media_id
where
  full_text like '%lb%'
  and user = 3166449535
  and lbs is not null
group by
  tweets.id
order by
  created_at

This uses datasette-rure for the regexp_match() function. Example output here: https://twitter.com/simonw/status/1249400425138155523

Related

• sqlite Identifying column combination patterns in a SQLite table - 2021-01-12
• sqlite Sort by number of JSON intersections - 2022-08-17
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• datasette Searching all columns of a table in Datasette - 2021-08-23
• sqlite Comparing database rows before and after with SQLite JSON functions - 2022-12-14
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• sqlite Tracking SQLite table history using a JSON audit log - 2024-02-26
• sql Building a Markdown summary of Django group permissions - 2021-06-03

Created 2020-04-21T09:43:38-07:00, updated 2020-04-22T06:36:01-07:00 · History · Edit