SQLite search inside a column with JSON inside

Question

I have an SQLite database where a column is called i18n and contains a JSON tree where some localized strings are contained. For example:

{"gr":{"name":"NAME_IN_GR"},"ru":{"name":"NAME_IN_RU"}}

Now I should make a search for a partial string typed by the user inside the name node of a passed localization. My initial attempt is to use a regular expression but I'm afraid it could be a bit slower (everything should happens while user is typing, even if the operation is throttled). Now I'm considering two other ways:

• create a virtual table using FTS? How it works?
• alter/make a copy of the db where these fields are splitted inside their own columns (ie RU,GR, IT and so on).

What's your suggestion and a simple implementation?

Answer 1

If your instance of sqlite was compiled with the JSON1 extension enabled, it's easy:

SELECT * FROM yourtable AS t
WHERE EXISTS (SELECT j.id FROM json_each(t.i18n) AS j
              WHERE json_extract(j.value, '$.name') LIKE 'NAME_IN%');

However, a slight variation of your second idea, pulling out all these JSON sub-objects into rows of a table (Instead of one column per language, which quickly becomes unwieldy as the number of languages grows), is likely the better option:

CREATE TABLE translations(original TEXT, language TEXT, translated TEXT
                        , PRIMARY KEY(original, language)) WITHOUT ROWID;
INSERT INTO translations VALUES ('name', 'ru', 'название');
-- etc.

Scalable, easier to inspect and update the data, lets you retrieve just the relevant bits, performs better, etc.