Recursive AI

Expand a SQLite JSON array into rows in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

Use json_each to query array elements as SQL rows without splitting the JSON string in C#.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
var json = "[\"red\",\"blue\"]";
var rows = await db.QueryAsync<string>($"""
    SELECT key || ': ' || value
    FROM json_each({json}) ORDER BY key
    """);
foreach (var row in rows)
    Console.WriteLine(row);

OneLite binds interpolated values as SQL parameters. {json} is sent separately from the SQL text.

Unzip the example, then dotnet run.

Prints each zero-based array position and its value:

Text
0: red
1: blue

The full program

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
var json = "[\"red\",\"blue\"]";
var rows = await db.QueryAsync<string>($"""
    SELECT key || ': ' || value
    FROM json_each({json}) ORDER BY key
    """);
foreach (var row in rows)
    Console.WriteLine(row);
 
var doc = """{"tags":["green","gold"]}""";
var tags = await db.QueryAsync<string>($"""
    SELECT key || ': ' || value
    FROM json_each({doc}, '$.tags') ORDER BY key
    """);
foreach (var tag in tags)
    Console.WriteLine(tag);
Text
0: red
1: blue
0: green
1: gold

The program expands the root array first and then reads the tags array inside a second document. Malformed JSON throws OneLiteException with the message malformed JSON.

The short answer

Put json_each in the FROM clause to get one row per element of a JSON array. Read key for the array position and value for the element. In C#, bind your JSON string with an interpolated parameter. Pass a path such as $.tags as the second argument when the array isn’t at the root.

How do you get each array index and value with SQLite json_each?

For an array, key starts at zero. Use ORDER BY key to print the items in array order, including when the array reaches double-digit positions. The query combines key and value into one string because OneLite’s QueryAsync<string> reads only the first selected column. That combined string is for display; when you need the fields separately, select them as separate columns and map the result to a row type.

The JSON function reference describes the type column, which distinguishes values such as integer, text, and null. value isn’t always text: numbers are SQL numbers and JSON null is SQL NULL. The example uses strings; concatenating a JSON null element in the same way would produce SQL NULL, not the literal word null.

How do you use json_each on a nested JSON array?

The second argument is a JSON path. $.tags starts at the document root ($) and selects its tags property. The function then returns the immediate elements of that array, with positions starting at zero inside tags. Leaving the path out for that same document would return the object’s tags property as one row, not its individual elements. See SQLite’s path syntax.

json_each doesn’t recursively flatten everything below the selected array. If an element is itself an array, that element is still one row. Use json_tree when you want to traverse descendants and track how they relate to their parents; use json_each when the path identifies the one array you want.

All onelite recipes