JSONB Operations

The JSON & JSONB page introduced the type itself and the difference between the text-preserving JSON and the parsed, indexable JSONB. This page goes deeper into the operators and functions PostgreSQL provides for actually working with JSONB data — pulling fields out, checking whether keys exist, updating a single nested value without rewriting the whole document, and building JSON back up from relational rows.

The core operators

Almost everything you do with JSONB in a query goes through one of these operators. The distinction between the arrow variants that return JSONB and the ones that return text trips people up constantly, so it is worth memorizing early.

Operator	Returns	Meaning
->	jsonb	Get a field or array element as JSONB
->>	text	Get a field or array element as text
#>	jsonb	Get the value at a nested path as JSONB
#>>	text	Get the value at a nested path as text
@>	boolean	Does the left JSONB contain the right JSONB?
<@	boolean	Is the left JSONB contained within the right JSONB?
?	boolean	Does the top-level key/element string exist?
?\|	boolean	Do any of these key/element strings exist?
?&	boolean	Do all of these key/element strings exist?
-	jsonb	Remove a key (or array index) from the document
\|\|	jsonb	Concatenate/merge two JSONB values

A quick example makes the -> vs. ->> distinction concrete. Suppose a products table has a JSONB attributes column shaped like { "color": "red", "dimensions": { "width": 12, "height": 8 {} } — a nested path like dimensions.width needs the #> / #>> path operators, which take an array of keys describing how deep to descend.

Getting nested JSONB fields

SQL

SELECT
  attributes -> 'color'              AS color_as_jsonb,
  attributes ->> 'color'             AS color_as_text,
  attributes #> '{dimensions,width}'  AS width_as_jsonb,
  attributes #>> '{dimensions,width}' AS width_as_text
FROM products
WHERE product_id = 42;

 color_as_jsonb | color_as_text | width_as_jsonb | width_as_text
----------------+---------------+----------------+---------------
 "red"          | red           | 12             | 12

Note that color_as_jsonb comes back with the surrounding quotes still attached — it is a JSONB string, not a plain text value. That is exactly the difference the -> / ->> split exists to make explicit: reach for -> when you plan to keep chaining JSONB operators or store the result back as JSONB, and ->> when you want a plain comparable/displayable text value.

Containment and key-existence queries

The @> containment operator is the one you will reach for most often when filtering rows by JSONB content — it asks whether the document on the left contains the structure on the right, matching regardless of key order or extra keys elsewhere in the document.

Filtering with containment and key existence

SQL

-- Find products whose attributes contain color: red
SELECT product_id, name
FROM products
WHERE attributes @> '{"color": "red"}';

-- Find products that have a "warranty_months" key at all
SELECT product_id, name
FROM products
WHERE attributes ? 'warranty_months';

-- Find products tagged with either "sale" or "clearance"
SELECT product_id, name
FROM products
WHERE attributes -> 'tags' ?| array['sale', 'clearance'];

Updating nested JSONB with jsonb_set()

Because JSONB is stored as a single value per row, a naive UPDATE that reassigns the whole column means constructing an entirely new document just to change one nested field. jsonb_set() avoids that: it takes the original document, a path array describing where to write, and a new value, and returns a copy with only that path replaced — everything else in the document is left untouched.

Updating a single nested field

SQL

UPDATE products
SET attributes = jsonb_set(attributes, '{dimensions,width}', '15')
WHERE product_id = 42;

-- jsonb_set's fourth argument controls whether a missing path
-- gets created; it defaults to true
UPDATE products
SET attributes = jsonb_set(attributes, '{warranty_months}', '24', true)
WHERE product_id = 42;

Removing a key entirely uses the - operator rather than jsonb_set(), and merging two documents together — useful for applying a batch of updates in one shot — uses the || concatenation operator.

Removing a key and merging documents

SQL

-- Drop the "warranty_months" key
UPDATE products
SET attributes = attributes - 'warranty_months'
WHERE product_id = 42;

-- Merge a batch of new attributes in; overlapping keys are overwritten
UPDATE products
SET attributes = attributes || '{"color": "blue", "on_sale": true}'
WHERE product_id = 42;

Building JSON from relational rows

The functions above all start from an existing JSONB value. jsonb_build_object() and jsonb_agg() go the other direction — constructing JSON out of ordinary relational columns and rows, which is useful whenever an API endpoint or a report needs a nested JSON shape assembled from a normalized schema.

Building a JSON object per row, then aggregating

SQL

SELECT jsonb_agg(
  jsonb_build_object(
    'id', o.order_id,
    'total', o.total_amount,
    'placed_at', o.created_at
  )
) AS orders_json
FROM orders o
WHERE o.customer_id = 7;

[
  {"id": 101, "total": 59.99, "placed_at": "2024-03-01T10:15:00"},
  {"id": 108, "total": 24.50, "placed_at": "2024-03-14T16:02:00"}
]

This pattern — group rows, build_object per row, agg across the group — is the standard way to shape a one-to-many relational result into a single JSON column, for instance returning a customer alongside an array of their orders in one query instead of stitching rows together in application code.

Index JSONB for containment queries

Filtering with @> or ? on a large table without an index means PostgreSQL has to inspect every row's document. A GIN index on the JSONB column makes those containment and key-existence lookups fast, the same way a B-tree index speeds up equality lookups on a plain column. The Index Types page covers GIN indexes, and how they differ from PostgreSQL's other specialized index types, in depth.

jsonb_set on a missing path with create_missing = false

By default jsonb_set() creates a path that does not yet exist. If you explicitly pass false as the fourth argument and the path is missing, it silently returns the original document unchanged rather than raising an error — worth knowing before you assume an UPDATE took effect.

-> and #> return JSONB; ->> and #>> return text — pick based on whether you need to keep chaining.
@> checks containment and is the operator to index for fast JSONB filtering.
jsonb_set() updates one path in place without rewriting the whole document.
jsonb_build_object() and jsonb_agg() go the other direction, assembling JSON from relational rows.