Filtering JSON with System.Data.SQLite and json_each()

Question

A SQLite database contains a column with JSON data like the following:

MyTable
______________________________________
| specs         | name   | date
--------------------------------------
| ["foo","bar"] | Test01 | 2018-02-05
| ["foo","bar"] | Test02 | 2018-02-01
| ["foo"]       | Test03 | 2018-02-03

Pseudo-code:

private static SQLiteConnection _sqliteDb;

using (var source = new SQLiteConnection($"Data Source={databasePath};Version=3;"))
{
    source.Open();

    _sqliteDb = new SQLiteConnection("Data Source=:memory:");
    _sqliteDb.Open();

    // copy db file to memory
    source.BackupDatabase(_sqliteDb, "main", "main", -1, null, 0);
    source.Close();
}

var cmd = new SQLiteCommand(query, _sqliteDb);

In order to filter on this column, the query looks like this:

var query = "SELECT * FROM MyTable, json_each(MyTable.specs) AS spec WHERE spec.value = 'bar'"

This error appears: SQLite error (1): no such table: json_each

How to use json1 extension with System.Data.SQLite.Core?

Yes, my query is similar to the one in 4.13.1. Examples using json_each() — Velcro
– Velcro, Commented Feb 5, 2018 at 8:29
Is specs the table name or the column name? (The other one is missing from the query.) — CL.
– CL., Commented Feb 5, 2018 at 18:37

Velcro · Accepted Answer · 2018-02-07 15:54:18Z

4

SQLite extensions weren't loaded.

Right after:

_sqliteDb.Open();

I must call the following in order to make json1 to work:

_sqliteDb.EnableExtensions(true);
_sqliteDb.LoadExtension("SQLite.Interop.dll", "sqlite3_json_init");

Also note that functions are registered per-connection.

answered Feb 7, 2018 at 15:54

Velcro

5561 gold badge9 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KansaiRobot Over a year ago

The LoadExtension is failing to find that dll. What could be the problem?

Collectives™ on Stack Overflow

Filtering JSON with System.Data.SQLite and json_each()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related