如何在System.Data.SQLite.Core中启用json1扩展以使用json_each()

That error pops up because SQLite's json_each (and all JSON-related functions) are part of the json1 extension, which isn't always enabled by default in System.Data.SQLite.Core. Here's how to get it working, step by step:

1. Ensure you're using a recent version of System.Data.SQLite.Core #

First, double-check your NuGet package version. Versions 1.0.113 and later include the json1 extension compiled directly into the core library—no need to mess with external DLLs. If you're on an older version, upgrade it first via NuGet.

2. Enable json1 via Connection String (Simplest Method) #

The easiest way to turn on json1 is to add the EnableJson1=True parameter to your SQLite connection string. This tells the library to activate the json1 extension automatically when opening the connection.

Example code:

using System.Data.SQLite;

var connectionString = "Data Source=your_database.db;Version=3;EnableJson1=True;";
using var connection = new SQLiteConnection(connectionString);
connection.Open();

// Now run your query with json_each, e.g.:
var query = @"
    SELECT t.id, j.value
    FROM your_table t, json_each(t.json_column) j
    WHERE j.value->>'key' = 'target_value'
";

using var command = new SQLiteCommand(query, connection);
using var reader = command.ExecuteReader();
// Process results here

3. Manual Extension Loading (If Connection String Method Fails) #

In rare cases (like older versions or restricted environments), you might need to load the json1 extension manually. First, enable extension loading in your connection string with LoadExtensionEnabled=True, then call LoadExtension on the connection.

Note: The extension filename varies by platform:

• Windows: sqlite3_json1.dll
• Linux: libsqlite3_json1.so
• macOS: libsqlite3_json1.dylib

Example code:

var connectionString = "Data Source=your_database.db;Version=3;LoadExtensionEnabled=True;";
using var connection = new SQLiteConnection(connectionString);
connection.Open();

// Load the json1 extension
connection.LoadExtension("sqlite3_json1");

// Your json_each query should now work

4. Verify the Extension is Active #

To confirm json1 is enabled, run a quick test query:

SELECT json_array(1, 2, 3) AS test_json;

If it returns [1,2,3], the extension is working. If you get an error about json_array not existing, double-check your setup.

Common Pitfalls to Avoid #

• If using Entity Framework Core with System.Data.SQLite, make sure your DbContext's connection string includes EnableJson1=True—EF Core uses the underlying SQLiteConnection under the hood.
• Don't confuse System.Data.SQLite.Core with Microsoft.Data.Sqlite (Microsoft's official SQLite provider)—they have different extension activation methods, and your question specifies the former.

内容的提问来源于stack exchange，提问作者Velcro