The arrow operators in SQL

Two arrows, one difference: one keeps the JSON quoting and one strips it.

SELECT
    payload -> '$.kind' AS as_json,
    payload ->> '$.kind' AS as_text,
    payload -> '$.tags' AS tag_array,
    payload ->> '$.tags[0]' AS first_tag,
    payload ->> '$.order.total' AS total
FROM webhooks
WHERE payload ->> '$.kind' = 'order.paid';

How it works

-> returns JSON, so a string comes back with its quotes.
->> returns a plain SQL value, which is what a comparison wants.
An integer on the right side indexes an array.

Keywords and builtins used here

AS
FROM
SELECT
WHERE

The run, in numbers

Lines: 8
Characters to type: 237
Tokens: 49
Three-star pace: 90 tpm

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

Type this snippet

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

← Previous Next →