Skip to content

JSON and JSONB

Jump to bottom
github-actions[bot] edited this page Sep 2, 2026 · 7 revisions

JSON and JSONB

JSON support is built into every SQLite-provider package. Type converters store .NET objects as JSON inside a SQLite column. Method translators let you call SQLite's built-in JSON functions from LINQ queries. The translators are registered automatically when you build the options.

Storing objects as JSON

When you have a .NET type that does not map to a simple SQLite column, you can serialize it to JSON and store the result in the database. The framework provides two converters for this.

SQLiteJsonConverter<T> - TEXT storage

SQLiteJsonConverter<T> serializes the value to a JSON string and stores it in a TEXT column. It is the simplest option and works with any SQLite tooling that can read text.

SQLiteJsonbConverter<T> - JSONB storage

SQLiteJsonbConverter<T> stores the value in a BLOB column using SQLite's built-in jsonb() and json() functions. JSONB is more compact than text and lets SQLite parse it without scanning for quotes or escape sequences, which can make JSON function calls faster.

Platform compatibility. JSONB needs SQLite 3.45 or newer, which most stock mobile OS builds do not ship yet, so use SQLite.Framework.Bundled or SQLite.Framework.Cipher to get SQLiteJsonbConverter<T> on any device or fall back to SQLiteJsonConverter<T> (TEXT) on the default package.

Both converters take a JsonTypeInfo<T> from a source-generated JsonSerializerContext, which keeps them compatible with Native AOT and trimming.

Setup

Create a JsonSerializerContext that includes all types you want to store as JSON:

[JsonSerializable(typeof(Address))]
[JsonSerializable(typeof(List<string>))]
[JsonSerializable(typeof(List<Address>))]
public partial class AppJsonContext : JsonSerializerContext;

Register the converter on the SQLiteOptionsBuilder before building the options:

SQLiteOptions options = new SQLiteOptionsBuilder("app.db")
    // TEXT column
    .AddTypeConverter<Address>(new SQLiteJsonConverter<Address>(AppJsonContext.Default.Address))
    // or for JSONB binary BLOB column
    // .AddTypeConverter<Address>(new SQLiteJsonbConverter<Address>(AppJsonContext.Default.Address))
    // collections work the same way
    .AddTypeConverter<List<string>>(new SQLiteJsonConverter<List<string>>(AppJsonContext.Default.ListString))
    .Build();

using var db = new SQLiteDatabase(options);

Registering a whole JSON graph

When a JSON-mapped type holds another complex type, projecting that nested type directly normally requires its own AddTypeConverter registration. Pass your JsonSerializerContext to AddJsonContext and the framework registers a converter for every type declared in it.

SQLiteOptions options = new SQLiteOptionsBuilder("app.db")
    .AddJsonContext(AppJsonContext.Default)
    .Build();

// Address has no AddTypeConverter call, but the projection works:
List<Address> shipTos = await db.Table<Test>()
    .Select(t => t.Order.ShipTo)
    .ToListAsync();

Both methods skip any type that already has a converter, so anything you registered through AddTypeConverter is left alone, including the root types declared in the context.

After that, any model with an Address property is handled automatically:

public class Contact
{
    [Key]
    [AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public Address HomeAddress { get; set; } = new();
}

public class Address
{
    public string Street { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
}

Reading and writing work the same as any other column:

await db.Table<Contact>().AddAsync(new Contact
{
    Name = "Alice",
    HomeAddress = new Address { Street = "1 Main St", City = "Springfield" }
});

Contact alice = await db.Table<Contact>().FirstAsync(c => c.Name == "Alice");
Console.WriteLine(alice.HomeAddress.City); // Springfield

JSON functions in queries

SQLite has a set of built-in JSON functions such as json_extract, json_set and json_valid. The framework exposes these through the SQLiteJsonFunctions static class.

Available functions

Method SQL produced
Extract<T>(json, path) json_extract(json, path)
Set(json, path, value) json_set(json, path, value)
Insert(json, path, value) json_insert(json, path, value)
Replace(json, path, value) json_replace(json, path, value)
Remove(json, path) json_remove(json, path)
Type(json, path) json_type(json, path)
Valid(json) json_valid(json)
Patch(json, patch) json_patch(json, patch)
ArrayLength(json) json_array_length(json)
ArrayLength(json, path) json_array_length(json, path)
Minify(json) json(json)
ToJsonb(json) jsonb(json)
ExtractJsonb<T>(json, path) jsonb_extract(json, path)

These methods throw InvalidOperationException at runtime. They are only valid inside a LINQ expression tree, where they are translated to SQL before execution.

Filtering on a JSON field

var errors = await db.Table<Log>()
    .Where(l => SQLiteJsonFunctions.Extract<string>(l.Data, "$.level") == "error")
    .ToListAsync();

Projecting a JSON value

var levels = await db.Table<Log>()
    .Select(l => SQLiteJsonFunctions.Extract<string>(l.Data, "$.level"))
    .ToListAsync();

Checking whether a column contains valid JSON

var valid = await db.Table<Log>()
    .Where(l => SQLiteJsonFunctions.Valid(l.Data))
    .ToListAsync();

Collection methods

When you store a List<T> or T[] as JSON, the framework also routes many standard LINQ, List<T> and Array methods to SQL using json_each() and other SQLite JSON functions. Everything runs on the database, not in memory.

Supported LINQ methods (Enumerable)

Scalar results (no predicate)

Method What it does
Any() True if the array is not empty
Count() Number of elements
First() / FirstOrDefault() First element
Last() / LastOrDefault() Last element
Single() / SingleOrDefault() The only element or null if there is not exactly one
ElementAt(i) Element at the given index
Min() / Max() Smallest or largest element
Sum() / Average() Sum or average of numeric elements

Scalar results (with predicate)

Method What it does
Any(x => ...) True if any element matches
All(x => ...) True if every element matches
Count(x => ...) Number of matching elements
First(x => ...) / FirstOrDefault(x => ...) First matching element
Last(x => ...) / LastOrDefault(x => ...) Last matching element
Single(x => ...) / SingleOrDefault(x => ...) The only matching element

Aggregate with selector

Method What it does
Min(x => x.Prop) Smallest value of a property
Max(x => x.Prop) Largest value of a property
Sum(x => x.Prop) Sum of a numeric property
Average(x => x.Prop) Average of a numeric property

Collection results

Method What it does
Where(x => ...) Filter elements
Select(x => ...) Project each element
SelectMany(x => x.Items) Flatten nested collections
OrderBy(x => ...) Sort ascending
OrderByDescending(x => ...) Sort descending
ThenBy(x => ...) Secondary sort ascending (after OrderBy)
ThenByDescending(x => ...) Secondary sort descending (after OrderBy)
GroupBy(x => ...) Group elements by a key
Distinct() Remove duplicates
Reverse() Reverse the order
Skip(n) Skip the first n elements
Take(n) Take the first n elements
Concat(other) Combine two collections
Union(other) Combine two collections, removing duplicates
Intersect(other) Keep only elements that appear in both
Except(other) Remove elements that appear in the other
ToList() Materialize as List<T>
ToArray() Materialize as T[]
ToHashSet() Materialize distinct values as HashSet<T>

The materialized collection can have a different element type from the stored collection. Built-in scalar, enum, nullable and date/time elements are read directly. Object elements need registered JSON type metadata, normally through AddJsonContext or AddJsonbContext.

Supported List<T> methods

Method What it does
Contains(item) True if the list contains the item
IndexOf(item) Index of the first occurrence or -1
LastIndexOf(item) Index of the last occurrence or -1
GetRange(index, count) A sub-list starting at the given index
Exists(x => ...) True if any element matches the predicate
Find(x => ...) First element matching the predicate
FindAll(x => ...) All elements matching the predicate
FindIndex(x => ...) Index of the first match or -1
FindLast(x => ...) Last element matching the predicate
FindLastIndex(x => ...) Index of the last match or -1
TrueForAll(x => ...) True if every element matches

Supported Array methods

Method What it does
Array.IndexOf(arr, item) Index of the first occurrence or -1
Array.LastIndexOf(arr, item) Index of the last occurrence or -1
Array.Exists(arr, x => ...) True if any element matches
Array.Find(arr, x => ...) First matching element
Array.FindAll(arr, x => ...) All matching elements
Array.FindIndex(arr, x => ...) Index of the first match or -1
Array.FindLast(arr, x => ...) Last matching element
Array.FindLastIndex(arr, x => ...) Index of the last match or -1
Array.TrueForAll(arr, x => ...) True if every element matches
Array.ConvertAll(arr, x => ...) Project each element

Examples

// simple list queries
bool hasTag = await db.Table<Product>()
    .Where(p => p.Tags.Contains("electronics"))
    .AnyAsync();

int tagCount = await db.Table<Product>()
    .Select(p => p.Tags.Count())
    .FirstAsync();

// predicate on simple types
List<Product> filtered = await db.Table<Product>()
    .Where(p => p.Tags.Any(t => t.StartsWith("elec")))
    .ToListAsync();

// predicate on complex types
List<Order> orders = await db.Table<Order>()
    .Where(o => o.Items.Any(i => i.Price > 100 && i.Category == "Books"))
    .ToListAsync();

// nested property access works too
bool hasLocal = await db.Table<Company>()
    .Select(c => c.Offices.Any(o => o.Address.City == "Springfield"))
    .FirstAsync();

// aggregate with selector
decimal maxPrice = await db.Table<Order>()
    .Select(o => o.Items.Max(i => i.Price))
    .FirstAsync();

// collection results
List<string> sorted = await db.Table<Product>()
    .Select(p => p.Tags.OrderBy(t => t).Take(3).ToList())
    .FirstAsync();

// chaining works, methods are combined into a single SQL subquery
string firstSorted = await db.Table<Product>()
    .Select(p => p.Tags.OrderBy(t => t).First())
    .FirstAsync();

// secondary sorting with ThenBy
string result = await db.Table<Order>()
    .Select(o => o.Items
        .OrderBy(i => i.Category)
        .ThenByDescending(i => i.Price)
        .First().Name)
    .FirstAsync();

// multiple chained operations become one query
int count = await db.Table<Product>()
    .Select(p => p.Tags
        .Where(t => t.Length > 3)
        .OrderBy(t => t)
        .Count())
    .FirstAsync();

// flatten nested collections with SelectMany
List<string> allTags = await db.Table<Company>()
    .Select(c => c.Departments.SelectMany(d => d.Tags))
    .FirstAsync();

// group by and count
int distinctGroups = await db.Table<Product>()
    .Select(p => p.Tags.GroupBy(t => t).Count())
    .FirstAsync();

Query-level SelectMany

SelectMany can flatten a JSON list or array column into the main query. Later filters, projections and ordering apply to the elements.

var expensiveItems = await db.Table<Order>()
    .SelectMany(o => o.Items)
    .Where(item => item.Price >= 100)
    .OrderBy(item => item.Price)
    .Select(item => new { item.Name, item.Price })
    .ToListAsync();

The result-selector overload can combine the row and element. A JSON string, byte[] or dictionary cannot be a query-level SelectMany source.

Dictionary methods

JSON dictionaries support ContainsKey, ContainsValue, the indexer and Contains of a KeyValuePair<TKey, TValue>. A key can be constant, captured or translated from a row expression.

var rows = await db.Table<SettingsRow>()
    .Where(r => r.Values.ContainsKey(r.ActiveKey)
        && r.Values.ContainsValue(10))
    .Select(r => r.Values[r.ActiveKey])
    .ToListAsync();

A null key throws ArgumentNullException, matching Dictionary<TKey, TValue>. Projecting the Keys or Values collection itself is not supported.

Property access on JSON columns

When you access a property on a JSON-stored object, the framework translates it to json_extract. This works in Where, Select, OrderBy and anywhere else you use a property:

// property access on a single JSON object
string city = await db.Table<Contact>()
    .Select(c => c.HomeAddress.City)
    .FirstAsync();
// SQL: SELECT json_extract(t0."HomeAddress", '$.City') ...

// property access on the result of a collection method
string street = await db.Table<Order>()
    .Select(o => o.Items.First(i => i.Price > 50).Name)
    .FirstAsync();

Method chaining

When you chain two or more methods on a JSON collection, they are combined into a single SQL subquery instead of nesting multiple subqueries. For example, .Where(...).OrderBy(...).Take(n) produces one SELECT ... FROM json_each(...) WHERE ... ORDER BY ... LIMIT n query.

This also means that combinations like .Where(...).Count(), .OrderBy(...).ThenBy(...).First() and .GroupBy(...).Count() all work and produce clean SQL.

What is not supported

These patterns are not translated to SQL and will either fall back to client-side evaluation or throw an error:

  • FindIndex(int startIndex, Predicate<T>) and similar predicate overloads that take a start index or count are not supported. Only the single-predicate overloads work.
  • OrderBy / OrderByDescending as the final result in a Select gives the C# return type IOrderedEnumerable<T>, which cannot be deserialized back to List<T>. Chain another method after it instead, like .First() or .Take(n).
  • List<T>.Reverse() in a Select binds to the void instance method instead of the LINQ extension. Use Enumerable.Reverse(list) with the static call syntax instead.
  • Zip is not supported.
  • GroupBy(x => x.Key, (key, group) => ...) with a result selector is not supported yet. You can use GroupBy(x => x.Key).Count() and similar aggregations.
  • Find(x => ...) and First(x => ...) return the raw JSON value from the database. If you access a property on the result (like .Street), it works. If you try to return the whole object, you get the JSON string, not the deserialized object.

Native AOT

SQLiteJsonConverter<T> and SQLiteJsonbConverter<T> both use JsonTypeInfo<T> for serialization, so they are fully compatible with Native AOT and trimming. The source generator also emits materializers for projected List<T>, array and HashSet<T> results. The framework keeps all public methods on SQLiteJsonFunctions and Enumerable rooted for the trimmer, so those methods are never removed from the output.

You do not need to do anything extra beyond providing a source-generated JsonSerializerContext as shown above.

Clone this wiki locally