PostgreSQL JSON and JSONB: Query, Index, and Update JSON Columns

json vs jsonb: which to use

PostgreSQL ships two JSON types that are often confused. The key distinction is storage format: json keeps the original text verbatim, while jsonb parses JSON into an internal binary representation at write time. That upfront cost pays dividends on every read — binary data is faster to process and can be indexed. The table below summarizes every material difference. In practice, the only scenario where json wins is when your application requires exact key ordering or must preserve duplicate keys in the same object — situations that arise almost exclusively in legacy or audit-logging contexts. For all new development, the correct default is jsonb.

Recommendation: Use jsonb for all new columns. Use json only if you need to preserve exact whitespace and key order (rare). If you are migrating an existing schema, you can alter a column in place: ALTER TABLE products ALTER COLUMN metadata TYPE jsonb USING metadata::jsonb. PostgreSQL will cast the stored text to the binary format transparently.

Create a table with a JSONB column

Declaring a jsonb column is identical to any other column type — just use JSONB as the type name. PostgreSQL enforces JSON syntax on every write, so malformed JSON is rejected at the database level before it ever reaches your application code. The example below creates a products table with a metadata JSONB column and inserts two rows with nested objects and arrays. Note that the JSON literals are passed as single-quoted strings — PostgreSQL automatically parses and validates them on insert. You can use validate your JSON structure before inserting to catch errors early.

CREATE TABLE products (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  metadata  JSONB
);

INSERT INTO products (name, metadata) VALUES
  ('Widget A', '{"price": 9.99, "tags": ["sale", "new"], "dims": {"w": 10, "h": 5}}'),
  ('Widget B', '{"price": 24.99, "tags": ["premium"], "dims": {"w": 20, "h": 15}}');

You can also insert NULL into a JSONB column (the field is absent) or the JSON value 'null' (the field is present with a null value) — these are distinct. Add a NOT NULL constraint on the column if you want to prevent missing metadata entirely, or use a DEFAULT ''::jsonb to ensure every row has at least an empty object. To learn more about how to convert JSON to SQL INSERT statements programmatically from Python or JavaScript, see the dedicated guide.

Query JSON fields: ->, ->>, and #> operators

PostgreSQL provides four path operators for navigating JSON structures. The arrow operators (-> and ->>) access a single key or array index. The hash-arrow operators (#> and #>>) navigate to a nested path using an array of key names. The double-arrow variants always return text, which you can cast to the correct SQL type with ::int, ::numeric, ::boolean, etc. Returning JSON (single-arrow) is useful when you want to pass the nested object to another JSON function or operator without converting to text first.

-- Get price as text
SELECT name, metadata ->> 'price' AS price FROM products;

-- Filter by nested field
SELECT name FROM products
WHERE (metadata -> 'dims' ->> 'w')::int > 12;

-- Access nested path
SELECT metadata #>> '{dims,w}' AS width FROM products;

-- Check if tag exists in array
SELECT name FROM products
WHERE metadata -> 'tags' ? 'sale';

A common mistake is comparing a ->> result to a number directly. Because ->> returns text, the comparison metadata ->> 'price' > 10 performs a lexicographic string comparison, not a numeric one. Always cast: (metadata ->> 'price')::numeric > 10. For array element access by index, use integer subscripts: metadata -> 'tags' -> 0 returns the first tag as JSON, metadata -> 'tags' ->> 0 returns it as text.

Containment and existence operators: @>, ?, ?|, ?&

Beyond path navigation, PostgreSQL JSONB supports four operators that test membership and key existence without navigating to a specific path. These operators are the ones that benefit most from GIN indexing (covered in the next section) — on a large table without an index, each query performs a full sequential scan; with a GIN index, the same query touches only a tiny fraction of rows. The containment operator @> is particularly powerful: it checks whether the left-hand JSONB value contains all key-value pairs of the right-hand value, recursively. This makes it ideal for filtering rows by any combination of attributes stored in a JSONB column without knowing the full document structure in advance.

-- @> containment: does the JSON contain this sub-document?
SELECT name FROM products
WHERE metadata @> '{"tags": ["sale"]}';

-- ? key existence
SELECT name FROM products
WHERE metadata ? 'price';

-- ?| any key exists
SELECT name FROM products
WHERE metadata ?| ARRAY['sale', 'premium'];

-- ?& all keys exist
SELECT name FROM products
WHERE metadata ?& ARRAY['price', 'tags'];

The ?, ?|, and ?& operators test for top-level key existence. To check whether a key exists inside a nested object, use the containment operator: metadata @> '{"dims": {"w": 10}}' matches rows where dims.w equals 10. The @< operator is the inverse of @> (is contained by). All of these operators work on both jsonb and on arrays of JSONB values, making them flexible for a wide range of filtering patterns. To understand how this compares to document-oriented approaches, see store JSON in MongoDB.

Create a GIN index for fast JSONB queries

A GIN (Generalized Inverted Index) on a JSONB column indexes every key-value pair inside every document in the column. When PostgreSQL evaluates a query using @>, ?, ?|, or ?&, it can use the GIN index to find matching rows directly rather than scanning the entire table. On a table with millions of rows, this can reduce query time from multiple seconds to single-digit milliseconds — a reduction of 10–100× or more depending on the selectivity of the filter. GIN indexes do increase write overhead (each insert or update must update the index), but for read-heavy workloads the trade-off is almost always worth it. Always run EXPLAIN ANALYZE to verify that PostgreSQL is actually using the index after creation.

-- GIN index covers @>, ?, ?|, ?& operators
CREATE INDEX products_metadata_gin ON products USING GIN (metadata);

-- For -> / ->> queries on a specific key, use a btree expression index
CREATE INDEX products_price ON products ((metadata ->> 'price'));

-- Query using the index automatically
SELECT name FROM products
WHERE metadata @> '{"tags": ["sale"]}';
-- Uses the GIN index — scans < 1% of rows on large tables

For path-specific queries using -> or ->>, a GIN index on the full column does not help — you need a btree expression index on the specific path expression, as shown above for price. You can create multiple expression indexes for your most-queried paths alongside a single GIN index for ad-hoc containment queries. Use jsonb_path_ops as the GIN operator class (USING GIN (metadata jsonb_path_ops)) for a smaller, faster index if you only need @> and not the key-existence operators.

Update JSONB fields with jsonb_set()

PostgreSQL does not support in-place mutation of individual JSON keys the way document databases do. Instead, you use the jsonb_set() function to produce a modified copy of the JSONB value and assign it back to the column in a standard UPDATE statement. The function signature is jsonb_set(target jsonb, path text[], new_value jsonb, create_missing bool). The path argument is a PostgreSQL text array written with curly-brace syntax: '{price}' for a top-level key, '{address,city}' for a nested key. The new_value must be a valid JSONB literal — string values must be double-quoted within the JSON: '"hello"', not 'hello'.

-- jsonb_set(target, path, new_value, create_missing)
UPDATE products
SET metadata = jsonb_set(metadata, '{price}', '19.99')
WHERE name = 'Widget A';

-- Add a new key
UPDATE products
SET metadata = jsonb_set(metadata, '{discount}', '"10%"', true)
WHERE name = 'Widget B';

-- Remove a key with - operator
UPDATE products
SET metadata = metadata - 'discount'
WHERE name = 'Widget B';

-- Append to a JSONB array
UPDATE products
SET metadata = jsonb_set(
  metadata,
  '{tags}',
  (metadata -> 'tags') || '["clearance"]'
)
WHERE name = 'Widget A';

The - operator removes a top-level key and returns the modified JSONB value. To remove a nested key, chain jsonb_set calls or use the #- operator with a path array: metadata #- ''{dims,w}'` removes the w key from the nested dims object. To merge two JSONB objects (shallow merge, right side wins on conflict), use the || concatenation operator: metadata || '{"color":"red"}'. For bulk updates that modify many rows, wrap the UPDATE in a transaction and consider updating in batches to avoid long-running locks on the table.

Expand a JSONB array to rows with jsonb_array_elements

When a JSONB column contains an array, you often need to process each element as a separate row — for example, to count tag frequencies, filter by a specific tag, or join array elements against another table. PostgreSQL provides jsonb_array_elements() as a set-returning function (SRF) that expands a JSONB array into one row per element. Its companion jsonb_array_elements_text() returns elements as plain text rather than JSONB, which is convenient when elements are strings. Both functions can be used directly in the SELECT list (they implicitly cross-join with the rest of the row) or in a LATERAL join for more explicit control. You can also build JSONB objects from columns using jsonb_build_object(), which is useful for constructing API-ready JSON responses directly in SQL — avoiding serialization work in the application layer.

-- Unnest a JSONB array into rows
SELECT name, jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products;
-- Returns one row per tag per product

-- Build a JSON object from columns
SELECT jsonb_build_object(
  'name', name,
  'price', (metadata ->> 'price')::numeric
) FROM products;

To count how many tags each product has, use jsonb_array_length(metadata -> 'tags') — this avoids unnesting and is faster for simple counts. To find all products that have more than one tag: WHERE jsonb_array_length(metadata -> 'tags') > 1. For aggregating across all unnested elements (e.g., counting total occurrences of each tag across all products), use a subquery or CTE with jsonb_array_elements_text and then GROUP BY. If you want to validate the schema of your JSONB documents before storing them, consider validate your JSON structure with JSON Schema at the application layer before inserting into PostgreSQL.

Frequently asked questions

What is the difference between json and jsonb in PostgreSQL?

json stores the raw JSON text exactly as received, preserving whitespace and key order. jsonb stores JSON in a parsed binary format — it does not preserve key order or duplicate keys, but it is faster to query and supports GIN indexes. For most use cases, jsonb is the better choice because it enables indexed queries (reducing scan time by 10–100× on large tables) and supports more operators. Use json only when exact text preservation is a requirement, which is rare. You can always convert: ALTER TABLE t ALTER COLUMN col TYPE jsonb USING col::jsonb.

How do I query a JSON field in PostgreSQL?

Use the -> and ->> operators. The -> operator returns a JSON value: metadata -> 'price' returns the price as a JSON number. The ->> operator returns a text value: metadata ->> 'price' returns '9.99' as a string. For nested paths, use #> (JSON) or #>> (text): metadata #>> '{address,city}' returns the city as text. Cast the text result to the correct type: (metadata ->> 'price')::numeric for numeric comparisons. See the convert JSON to SQL INSERT statements guide for how to populate these columns programmatically.

How do I index a JSONB column in PostgreSQL?

Create a GIN index: CREATE INDEX idx_name ON table USING GIN (column). GIN indexes cover the @>, ?, ?|, and ?& operators and make containment and key-existence queries fast. For queries using -> or ->> on a specific key, create a btree expression index: CREATE INDEX idx_price ON products ((metadata ->> 'price')). Run EXPLAIN ANALYZE on your query to confirm PostgreSQL is using the index. GIN indexes can reduce query time from full table scans to milliseconds on millions of rows.

How do I update a JSONB field in PostgreSQL?

Use jsonb_set(target, path, new_value): UPDATE products SET metadata = jsonb_set(metadata, '{price}', '19.99') WHERE id = 1. The path is an array of keys: '{address,city}' for nested fields. To create a new key if it doesn't exist, pass true as the fourth argument. To remove a key, use the - operator: metadata - 'key_name'. To merge two JSONB objects, use the || operator: metadata || '{"new_key":"value"}'.

When should I use PostgreSQL JSONB instead of a regular column?

Use JSONB when: the schema varies between rows (different products have different attributes), you're prototyping and the structure isn't finalized, or you're storing configuration or settings that differ per user. Use regular columns when: every row has the same fields, you need foreign keys or complex joins on those fields, or you need aggregate functions (SUM, AVG) — these work better on typed columns than on JSON text. A hybrid approach works well: fixed columns for frequently queried fields, JSONB for variable attributes. For a comparison with a document-first approach, see store JSON in MongoDB.

How do I convert a JSON array to rows in PostgreSQL?

Use jsonb_array_elements() to expand a JSONB array into a set of rows: SELECT jsonb_array_elements(metadata -> 'tags') AS tag FROM products. For text values, use jsonb_array_elements_text(): SELECT jsonb_array_elements_text(metadata -> 'tags') AS tag. To include the parent row's columns, use a lateral join or list the table columns before the function call. To count array elements: jsonb_array_length(metadata -> 'tags').