In SQLite, the ->> operator extracts a subcomponent from a JSON document and returns an SQL 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 an SQL representation of the specified subcomponent. To return a JSON 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 ]
        }
        ]' ->> '$[0]';

Result:

{"user":"Spike","age":30,"scores":[9,7,3]}

In SQLite, arrays are zero based, and so specifying [0] returns the first 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 ]
        }
        ]' ->> '$[0].scores';

Result:

[9,7,3]

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 ]
        }
        ]' ->> '$[0].scores[1]';

Result:

7

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