It should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON. This is an extension to the query language that adds support for encoding/decoding within the context of a query:

    SELECT DISTINCT user.name
    FROM user, json_each(user.phone)
    WHERE json_each.value LIKE '704-%';

It's pretty neat considering they rolled it all on their own: https://www.sqlite.org/src/artifact/552a7d730863419e

PS: If you haven't looked at the SQLite source before, check out their tests.