Menu

PostgreSQL JSONB: Operators, Queries and Indexes

Store and query JSON in PostgreSQL with jsonb: the -> and ->> operators, @> containment, jsonb_set and other updates, turning arrays into rows, GIN indexes, and json vs jsonb.

This page includes runnable editors. Edit, run, and see output instantly.

jsonb is PostgreSQL's binary JSON column type. Read fields with -> (returns jsonb) and ->> (returns text), find rows with the containment operator @>, and change fields with jsonb_set or ||. A GIN index makes those queries fast.

Orders 1 and 3 match. data->'customer' returns the nested object as jsonb, ->>'name' pulls the name out as text, and ->> plus a cast turns the total into a number.

Get values out of JSONB

OperatorReturnsExample
-> key or indexjsonbdata->'customer', data->'tags'->0
->> key or indextextdata->>'status'
#> pathjsonbdata#>'{customer,city}'
#>> pathtextdata#>>'{customer,city}'
[...] subscript (PostgreSQL 14+)jsonbdata['customer']['city']

Array indexes start at 0, and a negative index counts from the end (-> -1 is the last element). A missing key gives SQL NULL, not an error, so order 3 above has a NULL city.

Use ->> to compare with text or to cast: data->>'status' = 'paid'. Use -> to keep navigating. A common mistake is data->'status' = 'paid'. PostgreSQL reads 'paid' as JSON, an unquoted word is not valid JSON, and the query fails with "invalid input syntax for type json".

Find rows by JSON content

@> tests whether the left value contains the right one. It works at any depth and inside arrays, and a GIN index supports it:

Products 1 and 2 have size M and a price; only 2 costs more than 30. The search operators:

  • @> contains, <@ is contained in.
  • ? the key (or array string) exists, ?| any of the keys exist, ?& all of them exist.
  • @? and @@ run a JSON path query (PostgreSQL 12+), for comparisons inside the document.

Update fields inside JSONB

PostgreSQL rewrites the whole value on every update, but these functions let you describe only the change:

  • jsonb_set(target, path, new_value) replaces one value at a path.
  • - removes a key, and #- removes a nested path such as '{notify,sms}'.
  • || adds or overwrites top-level keys.

Since PostgreSQL 14 you can also assign through a subscript: SET prefs['notify']['email'] = 'false'.

Two traps:

  • jsonb_set returns NULL when the new value is SQL NULL, and the UPDATE then wipes the whole column. Pass COALESCE(to_jsonb(x), 'null'), or use jsonb_set_lax (PostgreSQL 13+), which stores a JSON null by default.
  • jsonb_set adds a missing last key, but not missing parents: setting '{a,b}' on {} leaves it unchanged. Subscript assignment does create them.

Turn JSON arrays into rows

jsonb_array_elements returns one row per array element, and jsonb_to_recordset turns an array of objects into typed columns. Since PostgreSQL 17, the standard JSON_TABLE does both in one place:

A-1 sold 3 and B-2 sold 1. Going the other way, jsonb_build_object('id', id, 'name', name) builds an object from columns and jsonb_agg collects rows into an array (see string_agg and array_agg).

Index JSONB with GIN

A GIN index on the whole column serves @>, ?, ?|, ?&, @? and @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

Write the query in the form the index supports. WHERE data @> '{"status": "paid"}' can use the GIN index. WHERE data->>'status' = 'paid' cannot; it needs the expression index. A range test such as (data->>'total')::numeric > 50 needs an expression index on that exact expression.

JSON vs JSONB

jsonjsonb
Stored asThe input textA parsed binary form
Whitespace, key order, duplicate keysKeptRemoved; the last duplicate wins
WritingSlightly faster (only validated)Parsed on input
Reading and queryingRe-parsed on every accessFast
@>, ? and GIN indexesNoYes

json returns the text unchanged; jsonb keeps only "a": 3 and reorders the keys. Use jsonb unless you must return the original text byte for byte, such as a stored webhook payload. Keep fields that every row has, or that need a foreign key, in normal columns. The official JSON functions reference lists every operator and function.

Frequently Asked Questions

What is the difference between -> and ->> in PostgreSQL?

-> returns the value as jsonb, so you can keep navigating (data->'customer'->'city'). ->> returns it as text, which is what you compare with a string or cast to a number: (data->>'total')::numeric.

How do I check if a JSONB array contains a value?

Use containment: data->'tags' @> '["gift"]', or data @> '{"tags": ["gift"]}' to test from the top of the document. For an array of strings, data->'tags' ? 'gift' also works. A GIN index on data speeds up the second form.

Does JSONB keep the order of keys?

No. jsonb stores keys in its own order, removes whitespace and keeps only the last of duplicate keys. If you need the original text back exactly, use the json type.

How do I check if a key exists in JSONB?

data ? 'key' is true when the top-level object has that key, even if its value is JSON null. ?| takes an array and is true if any key exists, ?& if all exist.

Can I add a foreign key or NOT NULL constraint to a field inside JSONB?

Not directly. A CHECK constraint can test a field (CHECK (data ? 'email')), but foreign keys need a real column. Fields that every row has and that other tables reference belong in ordinary columns.

Coddy programming languages illustration

Learn to code with Coddy

Get started