In SQLite, the json_extract() function extracts and returns one or more values from well-formed JSON.

We pass the JSON as an argument when we call the function, and it returns the applicable value/s.

We can specify one or more paths to extract from the JSON document.

Syntax

The syntax goes like this:

json_extract(X,P1,P2,...)

Where X represents the JSON document, and P1,P2,... are paths that we can use to extract specific parts of the JSON document.

Examples

Here’s a basic example to demonstrate:

SELECT json_extract('{ "a" : 1 }', '$');

Result:

{"a":1}

Here, I specified a path of $, which returns the whole JSON document.

Here’s an example with a larger JSON document:

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

Result:

{"dogs":[{"name":"Wag","scores":[7,9]},{"name":"Bark","scores":[3,4,8,7]},{"name":"Woof","scores":[3,2,1]}]}

Let’s change the path so that we return just the dogs array:

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

Result:

[{"name":"Wag","scores":[7,9]},{"name":"Bark","scores":[3,4,8,7]},{"name":"Woof","scores":[3,2,1]}]

Let’s select one of the elements within the array:

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.

Let’s go deeper again, and return just the name of the dog at that position in the array:

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

Specify Multiple Paths

The json_extract() function allows us to select 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"]

In this case, I returned the names of all the dogs in the dogs array.

All dog names are returned in an array.

Selecting a Non-Existent Path

If we point to a path that doesn’t exist, null is returned.

First, let’s set .nullvalue to NULL:

.nullvalue NULL

The .nullvalue dot command allows us to provide a string that will be used to replace null values. It’s one of several ways you can replace null values with a string in SQLite. In this case, I set it to NULL. Now, any null values will return NULL instead of a blank result.

Now let’s call json_extract(), but use a second argument that points to a non-existent path:

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

Result:

NULL

Invalid Paths

We’ll get an error if our path is not well-formed:

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

Result:

Runtime error: JSON path error near 'dogs'

In this case, I forgot to include $. at the front of the path.

Invalid JSON Documents

We’ll also get an error the JSON is not well-formed:

SELECT json_extract('{ "Dogs" : }', 
'$'
);

Result:

Runtime error: malformed JSON

This time the error tells us that our JSON is malformed.

Return Types

The SQLite documentation states the following:

If only a single path P1 is provided, then the SQL datatype of the result is NULL for a JSON null, INTEGER or REAL for a JSON numeric value, an INTEGER zero for a JSON false value, an INTEGER one for a JSON true value, the dequoted text for a JSON string value, and a text representation for JSON object and array values. If there are multiple path arguments (P1, P2, and so forth) then this routine returns SQLite text which is a well-formed JSON array holding the various values.

MySQL Compatibility

The SQLite documentation also warns us about a subtle incompatibility between the SQLite and MySQL implementations of the json_extract() function.

Specifically, it states:

The MySQL version of json_extract() always returns JSON. The SQLite version of json_extract() 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. In SQLite, if json_extract() has only a single 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.

Basically, this difference only becomes apparent when accessing individual values within the JSON that are strings or NULLs.