Home › API › SIMPLE_SQL_JSON

SIMPLE_SQL_JSON

JSON support using SQLite's JSON1 extension. Validate, query, and modify JSON data stored in columns.

View Source

Creation

make (a_db: SIMPLE_SQL_DATABASE)

Create JSON helper attached to database.

Validation

is_valid (a_json: READABLE_STRING_GENERAL): BOOLEAN

Check if string is valid JSON.

json_type (a_json: READABLE_STRING_GENERAL): STRING_8

Get JSON type: "object", "array", "string", "integer", "real", "true", "false", "null".

Extraction

extract (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8): detachable STRING_32

Extract value at JSON path (e.g., "$.name", "$.items[0]").

extract_integer (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8): INTEGER_64

Extract integer value at path.

extract_real (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8): REAL_64

Extract real value at path.

Modification

set (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8; a_value: detachable ANY): STRING_32

Set or replace value at path. Creates path if needed.

insert (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8; a_value: detachable ANY): STRING_32

Insert value at path (fails if path exists).

replace (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8; a_value: detachable ANY): STRING_32

Replace value at path (fails if path doesn't exist).

remove (a_json: READABLE_STRING_GENERAL; a_path: READABLE_STRING_8): STRING_32

Remove value at path.

Creation

json_object (a_pairs: ARRAY [TUPLE [key: READABLE_STRING_8; value: detachable ANY]]): STRING_32

Create JSON object from key-value pairs.

json_array (a_values: ARRAY [detachable ANY]): STRING_32

Create JSON array from values.

Aggregation

group_array (a_table, a_column: READABLE_STRING_8; a_where: detachable READABLE_STRING_8): STRING_32

Aggregate column values into JSON array.

group_object (a_table, a_key_column, a_value_column: READABLE_STRING_8): STRING_32

Aggregate into JSON object with key-value pairs.

Examples

Store and Query JSON

local
    json: SIMPLE_SQL_JSON
do
    create json.make (db)

    -- Store JSON in a column
    db.insert_into ("settings")
        .value ("user_id", 1)
        .value ("preferences", "{%"theme%":%"dark%",%"notifications%":true}")
        .execute

    -- Query JSON path in SQL
    result := db.query ("SELECT json_extract(preferences, '$.theme') as theme FROM settings WHERE user_id = 1")
end

Modify JSON

local
    json: SIMPLE_SQL_JSON
    prefs, new_prefs: STRING_32
do
    create json.make (db)
    prefs := "{%"theme%":%"dark%",%"notifications%":true}"

    -- Add new field
    new_prefs := json.set (prefs, "$.language", "en")
    -- Result: {"theme":"dark","notifications":true,"language":"en"}

    -- Update existing field
    new_prefs := json.set (new_prefs, "$.theme", "light")

    -- Remove field
    new_prefs := json.remove (new_prefs, "$.notifications")
end

Create JSON Programmatically

local
    json: SIMPLE_SQL_JSON
    obj: STRING_32
do
    create json.make (db)

    obj := json.json_object (<<
        ["name", "Alice"],
        ["age", 30],
        ["active", True]
    >>)
    -- Result: {"name":"Alice","age":30,"active":true}
end

Query with JSON Functions in SQL

-- Find users with dark theme
result := db.query ("SELECT * FROM settings WHERE json_extract(preferences, '$.theme') = 'dark'")

-- Get all unique themes
result := db.query ("SELECT DISTINCT json_extract(preferences, '$.theme') as theme FROM settings")

JSON Path Syntax

Use $ for root, .key for object access, [n] for array index.
Examples: $.name, $.items[0], $.users[0].email