SQLITE JSON GENERATED STORED COLUMN EXAMPLE

json.sql

CREATE TABLE IF NOT EXISTS offer_history (
    /* Primary keys */

    -- req offer_history id
    id TEXT NOT NULL PRIMARY KEY CHECK(id <> '' AND id = (data ->> '$.id')),

    /* Raw JSON data */

    -- req Raw json data
    data TEXT NOT NULL CHECK(data <> '' AND length(data) > 5),

    /* Fields extracted from JSON */

    -- req stored offer_history event_type
    offer_id TEXT NOT NULL CHECK(offer_id <> '') GENERATED ALWAYS AS (data ->> '$.offer.id') STORED,

    -- req stored offer_history event_type
    event_type TEXT NOT NULL CHECK(event_type <> '') GENERATED ALWAYS AS (data ->> '$.event_type') STORED,
    -- req updated_at the record was updated (ISO timestamp)
    updated_at TEXT NOT NULL GENERATED ALWAYS AS (data ->> '$.updated_at') STORED,

    /* Meta fields */

    -- opt Date the record was created in the database (unixtime in seconds)
    meta_created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    -- opt Date the record was updated in the database (unixtime in seconds)
    meta_updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')) CHECK(meta_updated_at >= meta_created_at),
    -- opt description
    meta_memo TEXT
) STRICT;


-- Indexes
CREATE INDEX IF NOT EXISTS offer_history_offer_id_idx ON offer_history (offer_id);

CREATE TRIGGER IF NOT EXISTS offer_history_updated_trig AFTER UPDATE ON offer_history
    BEGIN
        UPDATE offer_history SET meta_updated_at = strftime('%s', 'now') WHERE id = NEW.id;
    END;