MySQL JSON Shenanigans

In an effort to support out-of-date installations of our app, I had to keep a JSON column in our database. The column is obsolete, as are the values within it; but these installations continue to use it. So, knowing this, I decided to put the proper values into the column. I didn’t want to pollute the code of our services to do so, though. So I made it into a GENERATED column.

I ran into two issues.

First, MySQL doesn’t have a way to omit NULL values from a JSON array.

Second, the strings in the resulting array had \u0000 interspersed between each character. This is due to how MySQL represents strings in a regular column versus how they are represented in a JSON column. I’d have to look it up, but I think I recall that by default MySQL varchars are 16-bits per character, and JSON strings are UTF-8.

I “solved” the first issue by using IF() statements to represent all possible combinations of values. Luckily there were only 4. The only other solution I saw involved TRIGGERs. No, thank you.

The second issue was solved by using JSON_QUOTE() followed by JSON_UNQUOTE() to remove the \u0000 characters.

ALTER TABLE lectures
MODIFY COLUMN vimeo_video_links JSON GENERATED ALWAYS AS (
  IF(m3u8_url IS NOT NULL AND mp4_url IS NOT NULL,
  JSON_ARRAY(JSON_UNQUOTE(JSON_QUOTE(m3u8_url)), JSON_UNQUOTE(JSON_QUOTE(mp4_url))),
  IF(m3u8_url IS NOT NULL AND mp4_url IS NULL,
    JSON_ARRAY(JSON_UNQUOTE(JSON_QUOTE(m3u8_url))),
    IF(m3u8_url IS NULL AND mp4_url IS NOT NULL,
      JSON_ARRAY(JSON_UNQUOTE(JSON_QUOTE(mp4_url))),
      NULL
    )
  )
  )
) STORED;