The SQLite Arrow Operators for JSON Explained With Examples

SQLite added the -> and ->> operators in version 3.38.0, copying the shape of PostgreSQL's and MySQL's. They are shorthand for the extraction functions and they do read the same way, but the details of what they return, what the right operand may be and how they combine with other operators are SQLite's own. This article goes through them with test results from SQLite 3.54.1, because the differences from the other two databases are exactly the places where ported queries go wrong.

The two operators

select '{"a":"x","n":15,"f":1.5,"t":true,"z":null,"o":{"b":1},"l":[1,2]}' -> 'a';    -- "x"
select '{"a":"x","n":15,"f":1.5,"t":true,"z":null,"o":{"b":1},"l":[1,2]}' ->> 'a';   -- x

The first returns JSON, as text: a string keeps its double quotes, an object comes back as {"b":1}. The second returns an SQL value: the string without quotes, an integer as an integer, a real as a real, and an object or array as its JSON text, since SQL has nothing better. The result types, by typeof:

value in the document     ->> gives            -> gives
"x"                       text  x              text  "x"
15                        integer  15          text  15
1.5                       real  1.5            text  1.5
true                      integer  1           text  true
null                      NULL                 text  null
{"b":1}                   text  {"b":1}        text  {"b":1}
[1,2]                     text  [1,2]          text  [1,2]
(missing key)             NULL                 NULL

-> always gives text, so its numbers are the text 15 and its null is the text null; the only time -> gives SQL NULL is when the path does not exist. ->> gives you values you can compare and do arithmetic on. That makes ->> the one for WHERE, ORDER BY and SELECT lists, and -> the one for passing a part of a document to another JSON function (json_set, json_each, json_group_array) where you want it to remain JSON.

A JSON null deserves attention: '{"a":null}' ->> 'a' is null is 1, and '{"a":null}' -> 'a' is null is 0 because the latter is the text null. If you filter for "no value" with -> you will miss real nulls. And ->> merges the missing key and the JSON null into a single NULL, where json_type would tell them apart.

What the right side can be

Three forms of the right operand work, and they mean different things.

A path starting with $: '{"a":{"b":[10,20,{"d":"deep"}]}}' ->> '$.a.b[2].d' returned deep. All the path syntax from json_extract is available, including [#-1] for the last array element.

A bare label, with no $: '{"a":"x"}' -> 'a' is the same as -> '$.a'. The label is a literal key, never a path. I made a document with both a key named a.b and a nested a containing b, {"a.b":1,"a":{"b":2}}:

-> 'a.b'         1      (the key named "a.b")
->> '$.a.b'      2      (the path a, then b)
->> '$."a.b"'    1      (the quoted key in a path)

The bare form is convenient and slightly dangerous: copying a PostgreSQL habit such as doc -> 'a' -> 'b' works as a chain, but doc -> 'a.b' does not mean what a dotted-path reader expects. A label that looks like $a is an error (bad JSON path: '$a'), since a leading $ makes it a path.

An integer, for array positions: '[5,6,7]' -> 0 returned 5, ->> 0 also 5, and -> 9 (past the end) returned NULL. A negative integer counts from the end: '[5,6,7]' -> -1 returned 7. That differs from the extraction functions' [#-1] spelling and is shorter. On an object, an integer is treated as a key name: '{"0":"zero"}' -> 0 returned NULL, while -> '0' returned "zero".

Chaining

Because -> returns JSON, it chains to any depth, and the last link is usually ->>:

select '{"a":{"b":{"c":[10,20,{"d":"deep"}]}}}' -> 'a' -> 'b' -> 'c' -> 2 ->> 'd';   -- deep

That is five hops, each with its own label or index, and the result is the same as the single path ->> '$.a.b.c[2].d'. The chain re-parses the intermediate JSON text at each step, in principle, while the path walks once. For one or two hops it does not matter. For a hot query over many rows, use one path. On the product table, the nested lookup attrs ->> '$.specs.dims.w' is the form I used everywhere, and the chain attrs -> 'specs' -> 'dims' ->> 'w' gave the same answer.

Precedence, the trap

The arrows share the precedence level of the concatenation operator ||, and they bind tighter than arithmetic. Both facts appeared as results:

'{"a":2}' ->> 'a' * 3                       6        (arrow first, then multiply)
1 + '{"a":2}' ->> 'a'                       3        (arrow first, then add)
'x' || '{"a":"b"}' ->> 'a' || 'y'           malformed JSON

The third failed because || and ->> evaluate left to right at the same level: 'x' || '{"a":"b"}' was computed first, giving the text x{"a":"b"}, and then ->> 'a' tried to parse that as JSON. Whenever you mix || with an arrow, parenthesize the arrow: 'x' || ('{"a":"b"}' ->> 'a') || 'y'. Comparison operators bind looser than the arrows, so attrs ->> '$.brand' = 'Acme' needs no parentheses.

Comparison and type behavior

The result of ->> has no column affinity, which makes the comparison rules visible:

• '{"a":"2"}' ->> 'a' = 2 is 0: the value is the text 2, and SQLite does not equate text with a number when neither side has an affinity. '{"a":"2"}' ->> 'a' + 0 = 2 is 1, because arithmetic converts.

• '{"a":2}' ->> 'a' = 2 is 1.

• Comparing a number to a text constant is never true in the greater-than direction: attrs ->> '$.price' > '450' counted 0 rows on the products, while > 450 counted 19,856, since SQLite orders numbers before text.

• attrs -> '$.brand' = 'Acme' counted 0 (the left side is the text "Acme" with quotes), while attrs -> '$.brand' = '"Acme"' counted 40,000. Use ->> for comparisons with plain values.

A boolean through ->> is the integer 1 or 0, so where attrs ->> '$.in_stock' is true for in-stock rows (171,429 of 200,000), and = 'true' counted 0.

With JSONB

If the left side is a JSONB blob, the arrows work on it, and what they return is the same: jsonb('{"a":{"b":1}}') -> 'a' returned the text {"b":1}, not a blob. So an expression jsonb_col -> 'a' hands you JSON text, which you can pass to json_extract again or to jsonb() if you want to keep working in binary. There is no jsonb_ arrow. Details are in the JSONB format added in SQLite 3.45.

Compared with PostgreSQL and MySQL

If you move queries between databases, the biggest differences are the path operand (PostgreSQL's arrows take one key and its #> operators take a path, covered in querying JSONB with the arrow operators) and what ->> gives for non-strings. See JSON_EXTRACT and the arrow operators in MySQL for the MySQL side.

Indexes and generated columns

An index on (attrs ->> '$.sku') is used for a query that repeats attrs ->> '$.sku'. It was not used for attrs ->> 'sku' (the bare label) or for json_extract(attrs, '$.sku'): both showed SCAN products in explain query plan. Spell the field one way. The full story is in indexing JSON fields in SQLite with expression indexes.

When the arrows are not the right choice

You must run on SQLite older than 3.38. Use json_extract and json_type. Some Linux distributions and long-lived applications bundle old versions, so check select sqlite_version() in the environment that matters.

You need several values at once. json_extract accepts several paths in one call; the arrows take one.

You want to avoid the precedence puzzle. Writing (doc ->> 'a') with parentheses around every use costs nothing and removes the || trap.

The document is untrusted. Both forms raise malformed JSON on invalid text. Validate first (validating text as JSON with json_valid).

The operators are documented in the SQLite manual under the -> and ->> operators, and the precedence table in expression syntax. The overview of the functions is JSON functions in SQLite.