sqlitesql-order-bynode-sqlite3json-extractsqlite-json1

SQLite3 JSON1 Order by numeric index

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?

Solution

  • 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.