In SQLite, the -> operator extracts a subcomponent from a JSON document and returns a JSON representation of that subcomponent.

The -> operator was first introduced in SQLite version 3.38.0 (released on 22 February 2022).

Syntax

The syntax goes like this:

json -> path

Where json is the JSON document and path is the path to the subcomponent that we want to extract from it.

So we provide a JSON document to the left of the operator, and we specify the path we want to extract to its right.

The -> operator always returns a JSON representation of the specified subcomponent. To return a SQL representation, use the ->> operator instead.

Examples

Here’s a simple example to demonstrate how the -> operator works:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> '$';

Result:

{"name":"Wag","type":"Dog"}

In this case, I specified a path of '$' which returns the whole document.

Let’s specify another path:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> '$.type';

Result:

"Dog"

We can also omit the dollar sign and full stop altogether, like this:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> 'type';

Result:

"Dog"

Here it is with a larger JSON document:

SELECT '[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]' -> '$[2]';

Result:

{"user":"Jet","age":40,"scores":[50,38,67]}

In SQLite, arrays are zero based, and so specifying [2] returns the third array element.

If we only wanted to get that user’s scores, we could do this:

SELECT '[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]' -> '$[2].scores';

Result:

[50,38,67]

We can go even deeper and extract a specific score:

SELECT '[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]' -> '$[2].scores[1]';

Result:

38

Non-Existent Path

If the path doesn’t exist in the JSON, a null value is returned:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> '$.age';

Result:

null

Note that In SQLite, you can use .nullvalue to specify a string to output whenever a null value is returned. In my case, I had previously run the following command:

.nullvalue null

That specifies that the text null should be output whenever a null value is returned. That is why the above example output the text null. If I hadn’t done this, the result might have been blank.

Invalid JSON

If the first argument is not valid JSON, an error is thrown:

SELECT '{ "name" }' -> '$';

Result:

Runtime error: malformed JSON

Invalid Path

And if the second argument is not a valid path, an error is thrown:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> '$name';

Result:

Runtime error: JSON path error near 'name'

In this case, I forgot to include the full stop (.) between the dollar sign ($) and name.

However as mentioned, it is possible to omit the dollar sign and full stop altogether:

SELECT '{ "name" : "Wag", "type" : "Dog" }' -> 'name';

Result:

"Wag"