CODEWITHSUNDEEP
sqlitesql-order-bynode-sqlite3json-extractsqlite-json1
Lucien
Lucien

Reputation: 896

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?

Upvotes: 1

Views: 491

Answers (1)

forpas
forpas

Reputation: 164184

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.

Upvotes: 1

Related Questions