When using SQLite, we can use the following methods to extract data from a JSON document.

The json_extract() Function

As its name suggests, the json_extract() function extracts and returns one or more values from well-formed JSON.

Example:

SELECT json_extract('{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}', 
'$.dogs[1]'
);

Result:

{"name":"Bark","scores":[3,4,8,7]}

Arrays are zero based, and so the count starts at 0. Therefore, we specified [1] to get the second element within the dogs array, which happens to be a JSON object.

We can return just the name of the dog at that position in the array by adding to our path:

SELECT json_extract('{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}', 
'$.dogs[1].name'
);

Result:

Bark

We can use json_extract() to return multiple paths:

SELECT json_extract('{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}', 
'$.dogs[0].name',
'$.dogs[1].name',
'$.dogs[2].name'
);

Result:

["Wag","Bark","Woof"]

When we extract values from multiple paths, the values are returned in an array.

The -> Operator

The -> operator extracts a subcomponent from a JSON document and returns a JSON representation of that subcomponent.

Therefore, we can change the first example to this:

SELECT '{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}' -> '$.dogs[1]';

Result:

{"name":"Bark","scores":[3,4,8,7]}

The -> operator is subtly different to the json_extract() function:

• The -> operator always returns a JSON representation of the subcomponent.
• The json_extract() function only returns JSON if there are two or more path arguments (because the result is then a JSON array) or if the single path argument references an array or object.
• If there’s only one path argument and that path references a JSON null or a string or a numeric value, then json_extract() returns the corresponding SQL NULL, TEXT, INTEGER, or REAL value.

Therefore, here’s what happens when we extract a dog’s name from our JSON:

SELECT '{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}' -> '$.dogs[1].name';

Result:

"Bark"

This time it’s surrounded by quotes. That’s because it returned a JSON representation of the value. When we extracted the same value with json_extract() previously, we got an SQL representation of the value.

However, we can also use the ->> operator to return an SQL representation.

The ->> Operator

The ->> operator works the same way as the -> operator, except that ->> returns an SQL representation of the specified subcomponent. Specifically, it returns an SQL TEXT, INTEGER, REAL, or NULL value that represents the selected subcomponent, or NULL if the subcomponent does not exist.

Therefore, here’s what happens when we use the ->> operator to extract the dog’s name:

SELECT '{
    "dogs" : [
            { "name" : "Wag", "scores" : [ 7, 9 ] },
            { "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
            { "name" : "Woof", "scores" : [ 3, 2, 1 ] }
    ]
}' ->> '$.dogs[1].name';

Result:

Bark

It’s no longer enclosed in quotes.