I'm creating a nosql on top of sqlite. The sqlite row saves a blob (a bson). And I need to create indexing functionality on this blob field.

I'm doing it using the app-defined functions. For example, I define json_field('field_name', raw_blob_field) to return the value of that json.

This appears to work, however, I'm not sure if it is actually being used for indexing.

I have no way to debug it, because "Explain query plan" seems to only work with the command line interface. There is no way to debug it in c++ or rust (I'm actually using rusqlite).

With the command line interface, I don't know how to provide my custom function.

ok, actually, this is not that difficult,

I basically added logging in my own app-defined function.

when the query does scanning, my function will be triggered multiple times.

when it is doing indexing, my function won't be called.

You can add the UDF to SQL by taking the code and compiling it as a load library (called a DLL on Windows, shared object (so) on *nix, other names depending on the OS -- you did not specify the OS), then using the .load CLI command to load the load library into the CLI.

As long as you have created the index, ala:

create index idx on thetable (json_field('name', raw_blob_field));

Once you have created the index, you can use it once built. The function json_field is only required if (a) you call it explicitly or (b) you insert or update the table on which it is defined. (You may need to compile the CLI with SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION defined if the CLI you have does not have that defined -- you can find out by opening the database in the CLI. Without SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION you will get an error accessing the database).

If you issue SQL of the following form:

SELECT * FROM thetable WHERE json_field('name', raw_blob_field) == 'something';

then the index will be used. the function json_field does not need to be present unless you attempt to update the table (or otherwise use the function).

Slight correction.

if SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION is defined when you build the CLI, then you can open a database that has the unknown function in the schema (ie, in an index).

You can then use EXPLAIN and EXPLAIN QUERY PLAN in the CLI. The unknown function will only cause an error to be thrown if you attempt to execute a statement that needs it (such as updating the table (though you can do whatever the heck you want so long as the function that is missing is not required)).