json_ingest.sql in SQL

A JSON document arrives; relational rows come out, with the bad records set aside.

-- Ingesting a JSON payload into tables, keeping what fails.

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;

How it works

json_each over the array turns one document into one row per record.
json_valid and the path checks reject a record before it reaches the table.
The rejects land in their own table with the reason, instead of vanishing.

Keywords and builtins used here

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

The run, in numbers

Lines: 72
Characters to type: 1705
Tokens: 325
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 195 seconds.

Type this snippet

Step 2 of 2 in Encore, step 14 of 14 in JSON & search.

← Previous