Starting with SQLite version 3.38.0 (released on 22 February 2022), we can now use the -> and ->> operators to extract subcomponents of JSON documents.

The aim with these operators is to be compatible with the equivalent MySQL and PostgreSQL operators.

Also, starting with SQLite 3.38.0, the JSON functions are now built-ins. Therefore, it’s no longer necessary to use the -DSQLITE_ENABLE_JSON1 compile-time option to enable JSON support.

Syntax

The way these operators work is like this:

json -> path
json ->> path

Where json is the JSON document and path is the path 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 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 of the -> Operator

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 do it like this:

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

Result:

"Dog"

Example of the ->> Operator

Here’s what happens when we use ->> instead:

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

Result:

Dog

The value isn’t quoted like it was before. That’s because -> returns a JSON representation of the subcomponent and ->> returns an SQL representation.

The ->> operator returns an SQL TEXT, INTEGER, REAL, or NULL value that represents the selected subcomponent, or NULL if the subcomponent does not exist.

Here’s an example with an array:

SELECT '{ "scores" : [ 9, 7, 5 ] }' ->> '$.scores[2]';

Result:

5

If the value consists solely of an array, we can do this:

SELECT '[ 9, 7, 5 ]' ->> '2';

Result:

5

That returns the array element at the specified index (in this case 2).

Arrays are zero based (counting starts at 0), and that’s why 2 returns the third item.