JSON Support
Store and use JSON values in SQLite databases.
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
protocol SQLJSONExpressible
A type of SQL expression that is interpreted as a JSON value.
struct JSONColumn
A JSON column in a database table.
Access JSON subcomponents, and query JSON values, at the SQL level
The ->
and ->>
SQL operators are available on the SQLJSONExpressible
protocol.
static func jsonArrayLength(some SQLExpressible
) -> SQLExpression The number of elements in a JSON array, as returned by the
JSON_ARRAY_LENGTH
SQL function.static func jsonArrayLength(some SQLExpressible, atPath: some SQLExpressible
) -> SQLExpression The number of elements in a JSON array, as returned by the
JSON_ARRAY_LENGTH
SQL function.static func jsonExtract(some SQLExpressible, atPath: some SQLExpressible
) -> SQLExpression The
JSON_EXTRACT
SQL function.static func jsonExtract(some SQLExpressible, atPaths: some Collection<some SQLExpressible>
) -> SQLExpression The
JSON_EXTRACT
SQL function.static func jsonType(some SQLExpressible
) -> SQLExpression The
JSON_TYPE
SQL function.static func jsonType(some SQLExpressible, atPath: some SQLExpressible
) -> SQLExpression The
JSON_TYPE
SQL function.
Build new JSON values at the SQL level
static func json(some SQLExpressible
) -> SQLExpression Validates and minifies a JSON string, with the
JSON
SQL function.static func jsonArray(some Collection<some SQLExpressible>
) -> SQLExpression Creates a JSON array with the
JSON_ARRAY
SQL function.static func jsonArray(some Collection<any SQLExpressible>
) -> SQLExpression Creates a JSON array with the
JSON_ARRAY
SQL function.static func jsonObject(some Collection<(key: String, value: any SQLExpressible)>
) -> SQLExpression Creates a JSON object with the
JSON_OBJECT
SQL function. Pass key/value pairs with a Swift collection such as aDictionary
.static func jsonQuote(some SQLExpressible
) -> SQLExpression Returns a valid JSON string with the
JSON_QUOTE
SQL function.static func jsonGroupArray(some SQLExpressible, filter: (any SQLSpecificExpressible)?
) -> SQLExpression The
JSON_GROUP_ARRAY
SQL function.static func jsonGroupObject(key: some SQLExpressible, value: some SQLExpressible, filter: (any SQLSpecificExpressible)?
) -> SQLExpression The
JSON_GROUP_OBJECT
SQL function.
Modify JSON values at the SQL level
static func jsonInsert(some SQLExpressible, some Collection<(key: String, value: any SQLExpressible)>
) -> SQLExpression The
JSON_INSERT
SQL function.static func jsonPatch(some SQLExpressible, with: some SQLExpressible
) -> SQLExpression The
JSON_PATCH
SQL function.static func jsonReplace(some SQLExpressible, some Collection<(key: String, value: any SQLExpressible)>
) -> SQLExpression The
JSON_REPLACE
SQL function.static func jsonRemove(some SQLExpressible, atPath: some SQLExpressible
) -> SQLExpression The
JSON_REMOVE
SQL function.static func jsonRemove(some SQLExpressible, atPaths: some Collection<some SQLExpressible>
) -> SQLExpression The
JSON_REMOVE
SQL function.static func jsonSet(some SQLExpressible, some Collection<(key: String, value: any SQLExpressible)>
) -> SQLExpression The
JSON_SET
SQL function.
Validate JSON values at the SQL level
static func jsonIsValid(some SQLExpressible
) -> SQLExpression The
JSON_VALID
SQL function.