json_ingest.sql en SQL

Llega un documento JSON; salen filas relacionales, con los registros malos apartados.

-- Ingesta de un payload JSON en tablas, conservando lo que falla.

DROP TABLE IF EXISTS import_batch;
DROP TABLE IF EXISTS import_rejects;
DROP TABLE IF EXISTS import_products;

CREATE TABLE import_batch (
    id INTEGER PRIMARY KEY,
    document TEXT NOT NULL CHECK (JSON_VALID(document)),
    received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE import_products (
    sku TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

CREATE TABLE import_rejects (
    id INTEGER PRIMARY KEY,
    record TEXT NOT NULL,
    reason TEXT NOT NULL
);

INSERT INTO import_batch (document)
VALUES ('[{"sku":"TS-010","name":"Switch tester","price":24.0},'
        || '{"sku":"TS-011","name":"Coiled cable","price":39.5},'
        || '{"sku":"TS-012","name":"Broken record","price":-1},'
        || '{"name":"No sku at all","price":10.0}]');

WITH records AS (
    SELECT
        item.value AS record,
        item.value ->> '$.sku' AS sku,
        item.value ->> '$.name' AS name,
        item.value ->> '$.price' AS price
    FROM import_batch AS b
    JOIN JSON_EACH(b.document) AS item
)
INSERT INTO import_products (sku, name, price)
SELECT
    sku,
    name,
    price
FROM records
WHERE sku IS NOT NULL
    AND name IS NOT NULL
    AND price >= 0;

INSERT INTO import_rejects (record, reason)
SELECT
    item.value,
    CASE
        WHEN item.value ->> '$.sku' IS NULL THEN 'missing sku'
        WHEN item.value ->> '$.name' IS NULL THEN 'missing name'
        WHEN item.value ->> '$.price' < 0 THEN 'negative price'
        ELSE 'unknown'
    END
FROM import_batch AS b
JOIN JSON_EACH(b.document) AS item
WHERE item.value ->> '$.sku' IS NULL
    OR item.value ->> '$.name' IS NULL
    OR item.value ->> '$.price' < 0;

SELECT COUNT(*) AS imported
FROM import_products;

SELECT
    reason,
    COUNT(*) AS rejected
FROM import_rejects
GROUP BY reason;

Cómo funciona

json_each sobre el arreglo convierte un documento en una fila por registro.
json_valid y las verificaciones de ruta rechazan un registro antes de que llegue a la tabla.
Los rechazados caen en su propia tabla con el motivo, en lugar de esfumarse.

Palabras clave y builtins usados aquí

AND
AS
BY
CASE
CHECK
COUNT
CREATE
CURRENT_TIMESTAMP
DEFAULT
DROP
ELSE
END
EXISTS
FROM
GROUP
IF
INSERT
INTEGER
INTO
IS
JOIN
KEY
NOT
NULL
OR
PRIMARY
REAL
SELECT
TABLE
TEXT
THEN
VALUES
WHEN
WHERE
WITH

El intento, en números

Líneas: 72
Caracteres a escribir: 1711
Tokens: 325
Ritmo de tres estrellas: 100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 195 segundos.

Escribe este fragmento

Paso 2 de 2 en Bis; paso 14 de 14 en JSON y búsqueda.

← Anterior