SQLite3 JSON1 Order by numeric index

Question

I have a table like this:

TestTable
---------
data (TEXT)

All data values are JSON objects like { a:1, b:2, c:3 }.

I want to be able to query the database and ORDER BY data->b DESC without a full table scan (indexed).

Is this possible in SQLite JSON1?

Answer 1

Use the function json_extract():

SELECT * 
FROM TestTable
ORDER BY json_extract(data, '$.b') DESC;

See the demo.

If the values for b are quoted then cast to numeric:

SELECT * 
FROM TestTable
ORDER BY json_extract(data, '$.b') + 0 DESC;

See the demo.