Reading JSON out of a column in SQL

A JSON column is text until you ask a JSON function to look inside it.

CREATE TABLE webhooks (
    id INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
);

INSERT INTO webhooks (payload)
VALUES ('{"kind":"order.paid","order":{"id":41,"total":89.0},"tags":["eu"]}');

SELECT
    JSON_EXTRACT(payload, '$.kind') AS kind,
    JSON_EXTRACT(payload, '$.order.id') AS order_id,
    JSON_EXTRACT(payload, '$.order.total') AS total,
    JSON_EXTRACT(payload, '$.tags[0]') AS first_tag,
    JSON_EXTRACT(payload, '$.missing') AS absent
FROM webhooks;

How it works

A path starts at $, walks keys with . and arrays with [0].
json_extract returns SQL types: a JSON number becomes a number.
A path that matches nothing returns NULL, not an error.

Keywords and builtins used here

AS
CREATE
FROM
INSERT
INTEGER
INTO
KEY
NOT
NULL
PRIMARY
SELECT
TABLE
TEXT
VALUES

The run, in numbers

Lines: 15
Characters to type: 439
Tokens: 74
Three-star pace: 90 tpm

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

Type this snippet

Step 1 of 3 in Reading JSON, step 1 of 14 in JSON & search.