World cloud ignores many titles because they have special characters

[janmechtel]

This query will yield many titles that have non-json conform characters.

The should be replaced, but seems like sqlite doesn't support regexp so it's probably better to do when ingesting the data.

SELECT '["' || replace(replace(lower(replace(trim(replace(Title,'"','')), ' ', '", "')), CHAR(9), ''), CHAR(10), '') || '"]' AS array FROM ( SELECT DISTINCT Title FROM pings ) WHERE Title is not null AND json_valid('["' || replace(replace(lower(replace(trim(replace(Title,'"','')), ' ', '", "')), CHAR(9), ''), CHAR(10), '') || '"]') = 0

[janmechtel]

https://datasette.io/plugins/sqlite-regex

[janmechtel]

This could be combined with a kind of caching optimization, when extracting the words they could be cleaned in python and then stored in a title_words table

service, date, word

That would probably speed up retrival of word clouds a lot ;-)