SQLJSONExpressible

Overview

JSON values that conform to SQLJSONExpressible have two purposes:

• They provide Swift APIs for accessing their JSON subcomponents at the SQL level.

• When used in a JSON-building function such as jsonArray(_:) or jsonObject(_:), they are parsed and interpreted as JSON, not as plain strings.

To build a JSON value, create a JSONColumn, or call the asJSON property of any other expression.

For example, here are some JSON values:

// JSON columns:
JSONColumn("info")
Column("info").asJSON

// The JSON array [1, 2, 3]:
"[1, 2, 3]".databaseValue.asJSON

// A JSON value that will trigger a
// "malformed JSON" SQLite error when
// parsed by SQLite:
"{foo".databaseValue.asJSON

The expressions below are not JSON values:

// A plain column:
Column("info")

// Plain strings:
"[1, 2, 3]"
"{foo"

Access JSON subcomponents

JSON values provide access to the -> and ->> SQL operators and other SQLite JSON functions:

let info = JSONColumn("info")

// SELECT info ->> 'firstName' FROM player
// → 'Arthur'
let firstName = try Player
    .select(info["firstName"], as: String.self)
    .fetchOne(db)

// SELECT info ->> 'address' FROM player
// → '{"street":"Rue de Belleville","city":"Paris"}'
let address = try Player
    .select(info["address"], as: String.self)
    .fetchOne(db)

Build JSON objects and arrays from JSON values

When used in a JSON-building function such as jsonArray(_:) or jsonObject(_:), JSON values are parsed and interpreted as JSON, not as plain strings.

In the example below, we can see how the JSONColumn is interpreted as JSON, while the Column with the same name is interpreted as a plain string:

let elements: [any SQLExpressible] = [
    JSONColumn("address"),
    Column("address"),
]

let array = Database.jsonArray(elements)

// SELECT JSON_ARRAY(JSON(address), address) FROM player
// → '[{"country":"FR"},"{\"country\":\"FR\"}"]'
//     <--- object ---> <------ string ------>
let json = try Player
    .select(array, as: String.self)
    .fetchOne(db)

Accessing JSON subcomponents

• subscript(some SQLExpressible) -> SQLExpression

  The ->> SQL operator.

• func jsonExtract(atPath: some SQLExpressible) -> SQLExpression

  The JSON_EXTRACT SQL function.

• func jsonExtract(atPaths: some Collection<some SQLExpressible>) -> SQLExpression

  The JSON_EXTRACT SQL function.

• func jsonRepresentation(atPath: some SQLExpressible) -> SQLExpression

  Returns a valid JSON string with the -> SQL operator.

Supporting Types

• struct AnySQLJSONExpressible

  A type-erased SQLJSONExpressible.