The -> and ->> operators were introduced in SQLite version 3.38.0, which was released on 22 February 2022. Both operators are used for extracting subcomponents of JSON. But there’s a subtle difference between them.

The Difference

The difference between these operators goes like this:

• The -> operator always returns a JSON representation of the specified subcomponent
• The ->> operator always returns an SQL representation of the specified subcomponent

Example

Here’s an example that illustrates the difference between these two operators:

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

Result:

+-------+-----+
|  ->   | ->> |
+-------+-----+
| "Dog" | Dog |
+-------+-----+

We can see that -> returned the value enclosed in double quotes whereas ->> didn’t.

That’s because -> returned a JSON representation of the value, and ->> returned an SQL representation.

Numbers

Here’s an example that uses numbers:

SELECT 
    '{ "age" : 10 }' -> '$.age' AS "->",
    '{ "age" : 10 }' ->> '$.age' AS "->>";

Result:

+----+-----+
| -> | ->> |
+----+-----+
| 10 | 10  |
+----+-----+

Here’s what happens when we use the typeof() function to get the SQL type:

SELECT 
    typeof('{ "age" : 10 }' -> '$.age') AS "->",
    typeof('{ "age" : 10 }' ->> '$.age') AS "->>";

Result:

+------+---------+
|  ->  |   ->>   |
+------+---------+
| text | integer |
+------+---------+

However, if we use json_type(), we’ll get the JSON type:

SELECT 
    json_type('{ "age" : 10 }' -> '$.age') AS "->",
    json_type('{ "age" : 10 }' ->> '$.age') AS "->>";

Result:

+---------+---------+
|   ->    |   ->>   |
+---------+---------+
| integer | integer |
+---------+---------+

Here’s an example that uses a real number:

SELECT 
    typeof('{ "age" : 1.2 }' -> '$.age') AS "->",
    typeof('{ "age" : 1.2 }' ->> '$.age') AS "->>";

Result:

+------+------+
|  ->  | ->>  |
+------+------+
| text | real |
+------+------+

And with json_type():

SELECT 
    json_type('{ "age" : 1.2 }' -> '$.age') AS "->",
    json_type('{ "age" : 1.2 }' ->> '$.age') AS "->>";

Result:

+------+------+
|  ->  | ->>  |
+------+------+
| real | real |
+------+------+

Null Values

If the JSON document contains null, then -> will return the JSON representation of null, and ->> will simply return a null value.

Here’s an example to demonstrate what I mean:

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

Result:

+------+-----+
|  ->  | ->> |
+------+-----+
| null |     |
+------+-----+

By default, the SQLite command line interface (CLI) returns the empty string whenever a null value is returned. So we can see from our example that -> returned the actual JSON value null, whereas ->> returned an actual null value.

To further demonstrate this, we can set our .nullvalue to something other than the empty string:

.nullvalue n/a

Now let’s run the previous query again:

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

Result:

+------+-----+
|  ->  | ->> |
+------+-----+
| null | n/a |
+------+-----+

This time n/a was output for the ->> operator instead of the empty string.

And here’s what happens when we pass the output to the typeof() and json_type() functions:

SELECT 
    typeof('{ "name" : "Wag", "type" : null }' -> '$.type') AS "->",
    typeof('{ "name" : "Wag", "type" : null }' ->> '$.type') AS "->>";

SELECT 
    json_type('{ "name" : "Wag", "type" : null }' -> '$.type') AS "->",
    json_type('{ "name" : "Wag", "type" : null }' ->> '$.type') AS "->>";

Result:

+------+------+
|  ->  | ->>  |
+------+------+
| text | null |
+------+------+
+------+-----+
|  ->  | ->> |
+------+-----+
| null | n/a |
+------+-----+

An Alternative: json_extract()

Another way to extract values from a JSON document in SQLite is to use the json_extract() function. This function works slightly differently to the -> and ->> in that the return type depends on the context.

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.