JSON Support

Store and use JSON values in SQLite databases.

JSON.md

Overview

SQLite and GRDB can store and fetch JSON values in database columns. Starting iOS 16+, macOS 10.15+, tvOS 17+, and watchOS 9+, JSON values can be manipulated at the database level.

Store and fetch JSON values

JSON columns in the database schema

It is recommended to store JSON values in text columns. In the example below, we create a jsonText column with create(table:options:body:):

try db.create(table: "player") { t in
    t.primaryKey("id", .text)
    t.column("name", .text).notNull()
    t.column("address", .jsonText).notNull() // A JSON column
}

Strict and flexible JSON schemas

Codable Records handle both strict and flexible JSON schemas.

For strict schemas, use Codable properties. They will be stored as JSON strings in the database:

struct Address: Codable {
    var street: String
    var city: String
    var country: String
}

struct Player: Codable {
    var id: String
    var name: String

    // Stored as a JSON string
    // {"street": "...", "city": "...",  "country": "..."} 
    var address: Address
}

extension Player: FetchableRecord, PersistableRecord { }

For flexible schemas, use String or Data properties.

In the specific case of Data properties, it is recommended to store them as text in the database, because SQLite JSON functions and operators are documented to throw errors if any of their arguments are binary blobs. This encoding is automatic with text:

// JSON String property
struct Player: Codable {
    var id: String
    var name: String
    var address: String // JSON string
}

extension Player: FetchableRecord, PersistableRecord { }

// JSON Data property, saved as text in the database
struct Team: Codable {
    var id: String
    var color: String
    var info: Data // JSON UTF8 data
}

extension Team: FetchableRecord, PersistableRecord {
    // Support SQLite JSON functions and operators
    // by storing JSON data as database text:
    static func databaseDataEncodingStrategy(for column: String) -> DatabaseDataEncodingStrategy {
        .text
    }
}

Manipulate JSON values at the database level

SQLite JSON functions and operators are available starting iOS 16+, macOS 10.15+, tvOS 17+, and watchOS 9+.

Functions such as JSON, JSON_EXTRACT, JSON_PATCH and others are available as static methods on Database: json(_:), jsonExtract(_:atPath:), jsonPatch(_:with:), etc.

See the full list below.

JSON table-valued functions

The JSON table-valued functions json_each and json_tree are not supported.

JSON Values

Access JSON subcomponents, and query JSON values, at the SQL level

The -> and ->> SQL operators are available on the SQLJSONExpressible protocol.

Build new JSON values at the SQL level

Modify JSON values at the SQL level

Validate JSON values at the SQL level