SQLite 3 reference · JSON Functions

SQLite -> and ->> JSON Operators

Extract from JSON with -> (returns JSON text) or ->> (returns an SQL value). Paths can be '$.key' or just a key or index.

SQLite 3 Since 3.38.0

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

json -> path  |  json ->> path

Parameters

Parameter	Description
`json`	JSON text (or JSONB).
`path`	A path like `'$.a.b'`, a key like `'a'`, or an array index like `2`.

Return value

->: JSON text. ->>: an SQL text, integer, real or NULL.

Examples

-> vs ->>Try it yourself

SELECT '{"name":"Ada","tags":["math","code"]}' -> '$.tags' AS as_json,
       '{"name":"Ada","tags":["math","code"]}' ->> '$.tags[0]' AS first_tag,
       '{"name":"Ada"}' ->> 'name' AS by_key;

Output

as_json | first_tag | by_key
["math","code"] | math | Ada

Filter rows by a JSON fieldTry it yourself

WITH events(payload) AS (VALUES ('{"type":"click","x":10}'), ('{"type":"view","x":3}'))
SELECT payload ->> 'type' AS type, payload ->> 'x' AS x
FROM events
WHERE payload ->> 'x' > 5;