---
title: "Expand a SQLite JSON array into rows in C#"
url: https://recursiveai.org/onelite/cookbook/expand-sqlite-json-array-into-rows/
description: "Use SQLite json_each to return array positions and values in C#, including arrays inside a JSON object."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/expand-sqlite-json-array-into-rows/expand-sqlite-json-array-into-rows.zip)

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

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/docs/happy-path/#-binds-parameters). `{json}` is sent separately from the SQL text.

Unzip [the example](https://recursiveai.org/onelite/cookbook/expand-sqlite-json-array-into-rows/expand-sqlite-json-array-into-rows.zip), then `dotnet run`.

Prints each zero-based array position and its value:

```text
0: red
1: blue
```

## The full program

```csharp
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`.

## Related recipes

- [Query SQLite with a recursive CTE](https://recursiveai.org/onelite/cookbook/query-sqlite-with-recursive-cte/)
- [Create a SQLite database in C#](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)

## 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](https://recursiveai.org/onelite/docs/happy-path/).

The [JSON function reference](https://www.sqlite.org/json1.html#jeach) 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](https://www.sqlite.org/json1.html#path_arguments).

`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.
