Querying JSONB With the Arrow Operators in PostgreSQL

The two arrow operators are the first thing anyone learns about JSONB, and they are enough for a lot of work. They are also the source of a handful of small, repeatable mistakes: a cast in the wrong place, a text comparison that looks numeric, a typo that silently returns nothing. This article walks through -> and ->> against a real table, shows what they return in the odd cases, and lists the traps with the error messages they produce, so you recognize them when they appear in your own queries.

Two operators, two return types

Both operators take a document on the left and a key or position on the right. The difference is what comes back:

• -> returns jsonb. The result is still a JSON value, so you can keep going: attrs -> 'specs' -> 'dims'.

• ->> returns text. The result is a plain SQL string, or NULL.

On the first row of the table:

select attrs -> 'brand'  as arrow,
       attrs ->> 'brand' as arrow_text,
       pg_typeof(attrs -> 'brand')  as t1,
       pg_typeof(attrs ->> 'brand') as t2
from products where id = 1;

  arrow   | arrow_text |  t1   |  t2  
----------+------------+-------+------
 "Globex" | Globex     | jsonb | text

The first column carries the double quotes of a JSON string, and the second is the plain text. The quotes are the giveaway that you are looking at JSON. If your application shows strings with stray quotes around them, a -> is hiding where a ->> belongs.

The rule I use: chain with -> and finish with ->>. In attrs -> 'specs' -> 'dims' ->> 'w', the first two arrows descend through objects that must stay JSON, and the last pulls out a value as text. On row 1 that returned 11.

Arrays use the same operators

When the right operand is an integer, the arrows index into an array, counting from zero:

select attrs -> 'tags' -> 0  as first_tag,
       attrs -> 'tags' ->> -1 as last_tag
from products where id = 1;

The result was "sale" as JSON and eco as text. A negative number counts from the end, so -1 is the last element. This is the quickest way to read a known position, and it is the wrong way to find an element by value; for that you want containment or unnesting, covered in querying arrays inside JSONB.

What you get when the answer is nothing

Operators on JSONB almost never raise an error for a shape mismatch. They return NULL. I tried the cases that people hit:

'[10,20,30]'::jsonb -> 5            NULL   (index past the end)
'[10,20,30]'::jsonb -> -1           30
'[10,20,30]'::jsonb -> 'a'          NULL   (text key on an array)
'{"a":1}'::jsonb    -> 0            NULL   (integer on an object)
'5'::jsonb          -> 'a'          NULL   (arrow on a scalar)
attrs -> 'nope'                     NULL   (missing key)

This is the design: the operators are meant to be forgiving, so a query over a heterogeneous column does not fail on the first odd row. The price is that a typo disappears. attrs ->> 'brnad' = 'Acme' runs, returns no rows and gives no sign that the key is misspelled. If you can, put the key names in a generated column or a view so that a typo is a compile error, and test with a count: select count(*) from products where attrs ->> 'brand' is null should be small.

There is one more case: the JSON null. '{"a":null}'::jsonb -> 'a' returns the JSONB value null, which is not SQL NULL, while ->> 'a' returns SQL NULL. So where attrs -> 'a' is null finds rows where the key is missing and misses rows where it is present as null. If you mean either, use ->>, or jsonb_typeof(attrs -> 'a') to tell them apart; it returns 'null' for the JSON null.

Objects and arrays survive ->> as text: '{"a":{"b":1}}'::jsonb ->> 'a' gave {"b": 1} as a string. That is occasionally useful and often a sign that you wanted ->.

The traps

The cast binds to the key

:: binds tighter than the arrow. This looks reasonable and fails:

select attrs ->> 'price'::numeric from products limit 1;

ERROR:  invalid input syntax for type numeric: "price"

Postgres cast the string 'price' to numeric before the operator ran. Parenthesize the operator expression, then cast:

select (attrs ->> 'price')::numeric as price from products where id = 1;

which returned 371.47. Arithmetic has the same problem in a different shape. attrs ->> 'price' + 1 fails with invalid input syntax for type integer: "price", since + binds tighter than ->>. Wrap the arrow expression in parentheses before you do anything to the result.

Comparing text as if it were a number

->> gives text, and text sorts alphabetically. These two queries differ only in the cast:

select count(*) from products where (attrs ->> 'price')::numeric > 450;  -- 20152
select count(*) from products where  attrs ->> 'price'          > '450'; -- 44207

Both ran and returned different counts. The second one counts any price whose first character sorts after 4, which includes '51.20'. The same thing happens in ORDER BY. I selected the top three prices three ways:

order by attrs ->> 'price' desc               99.99, 99.99, 99.99
order by (attrs ->> 'price')::numeric desc    500.00, 500.00, 500.00
order by attrs -> 'price' desc                500.00, 500.00, 500.00

The first is wrong. Ordering by the JSONB value attrs -> 'price' is correct, because JSONB compares numbers as numbers, but it is hard to read and it cannot use a btree index on the numeric expression, which is why the cast form is the one to index. Whatever you pick, compare numbers as numbers and dates as dates.

A cast also fails on bad data. If one row has "price": "free", (attrs ->> 'price')::numeric raises an error for the whole statement, even for rows the WHERE clause would have excluded, because the planner is free to evaluate the cast before the filter. Guard it with case when jsonb_typeof(attrs -> 'price') = 'number' then (attrs ->> 'price')::numeric end, or keep clean data with a CHECK constraint on the column.

Booleans and the text true

attrs ->> 'in_stock' returns the text true or false, which casts to boolean, and (attrs ->> 'in_stock')::boolean counted 171,429 rows. You can also compare the JSONB values directly: attrs -> 'in_stock' = 'true'::jsonb returned the same count. Both are correct. Do not compare the text to the string 't', which is what psql prints for a boolean.

The operator is not the index

An index on the column does not speed up an arrow expression. where attrs ->> 'sku' = 'SKU-123456' scanned the table in 19 ms even with a GIN index on attrs. To make an arrow expression fast, index the expression itself, create index on products ((attrs ->> 'sku')), and write the query with the identical expression. Containment (@>) is what the GIN index answers, and indexing JSONB with GIN and when it helps explains the split. You will use the arrows in the select list and for joins on a keyed field far more than in filters on a big table.

A few patterns that work

Group by a field: select attrs ->> 'brand' as brand, count(*) from products group by 1 order by 2 desc, 1 returned five brands with 40,000 rows each. A positional group by 1 keeps the expression in one place.

Filter by several values with in: where attrs ->> 'brand' in ('Acme', 'Hooli').

Treat missing as different from a value, with is distinct from: attrs ->> 'brand' is distinct from 'Acme' includes rows where the brand is missing, while <> 'Acme' drops them, since NULL <> 'Acme' is NULL.

Reach three levels in with attrs -> 'specs' -> 'dims' ->> 'w', or use the path operator attrs #>> '{specs,dims,w}' for the same result; the two return the same value, and extracting nested JSONB values with path operators describes when the path form reads better. PostgreSQL 14 added subscripting, attrs['specs']['dims']['w'], which returns jsonb. For the text form, still use ->> at the end.

If you need to edit a value rather than read it, move on to updating one key inside a JSONB column. For a quick way to look at a document before you write a query, the JSON formatter pretty prints one in the browser.

When arrows stop being the right tool

Arrows are the right tool for a known key at a known depth. They get awkward in these situations.

A key whose name or position varies. If the path depends on data, the arrows do not compose. jsonb_each, jsonb_path_query or JSON_TABLE fit better.

Anything inside an array of objects. attrs -> 'items' -> 0 ->> 'sku' reads the first item and says nothing about the others. Use unnesting or a jsonpath filter (expanding JSONB into rows with jsonb_to_recordset).

Searches over many rows. An arrow in a WHERE on a large table is a scan unless you index that exact expression. For repeated filters on one or two fields, promote them to columns.

Code that must fail loudly. Because the operators return NULL on a mismatch, a schema change goes unnoticed. If a missing key should be an error, use jsonb_path_query in strict mode, which raised JSON object does not contain key "b" in my test, or validate documents with a CHECK constraint on the way in.

The operators are documented in the PostgreSQL manual under JSON functions and operators, and the precedence rules that explain the cast trap are in the operator precedence table.