Postgres JSONB Operators and Functions: A Reference

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  attrs jsonb NOT NULL DEFAULT '{}'
);
 
INSERT INTO products (name, attrs) VALUES
('Laptop Pro 14',  '{"brand":"Acme","price":1899.00,"specs":{"ram_gb":16,"cpu":"M3"},"tags":["laptop","portable"],"in_stock":true}'),
('Desk Lamp',      '{"brand":"Lumen","price":49.99,"specs":{"watts":9},"tags":["lighting","desk"],"in_stock":true}'),
('Mechanical Kbd', '{"brand":"Acme","price":129.00,"specs":{"switches":"brown","keys":87},"tags":["keyboard","desk"],"in_stock":false}'),
('Monitor 27',     '{"brand":"ViewCo","price":399.00,"specs":{"panel":"IPS","hz":144},"tags":["display"],"discount":null}');

SELECT
  attrs -> 'brand'   AS as_jsonb,   -- "Acme"  (jsonb, quoted)
  attrs ->> 'brand'  AS as_text     -- Acme    (text, unquoted)
FROM products
WHERE name = 'Laptop Pro 14';

-- Navigate two levels, then extract as text
SELECT name, attrs -> 'specs' ->> 'cpu' AS cpu
FROM products
WHERE attrs -> 'specs' ? 'cpu';

SELECT
  attrs -> 'tags' -> 0    AS first_tag_jsonb,   -- "laptop"
  attrs -> 'tags' ->> 0   AS first_tag_text,    -- laptop
  attrs -> 'tags' ->> -1  AS last_tag           -- portable
FROM products
WHERE name = 'Laptop Pro 14';

SELECT
  attrs #> '{specs,ram_gb}'   AS as_jsonb,   -- 16
  attrs #>> '{specs,ram_gb}'  AS as_text     -- 16
FROM products
WHERE name = 'Laptop Pro 14';

SELECT attrs #>> '{tags,0}' AS first_tag FROM products;

-- Works, but cannot use a plain index on the column
SELECT name FROM products
WHERE (attrs ->> 'price')::numeric > 100;
 
-- Casting the literal instead keeps things comparable as jsonb
SELECT name FROM products
WHERE attrs -> 'price' > '100'::jsonb;

-- Does this top-level key exist?
SELECT name FROM products WHERE attrs ? 'discount';
 
-- Do ANY of these keys exist?
SELECT name FROM products WHERE attrs ?| array['discount','warranty'];
 
-- Do ALL of these keys exist?
SELECT name FROM products WHERE attrs ?& array['brand','price'];

SELECT name FROM products WHERE attrs -> 'specs' ? 'ram_gb';

SELECT
  name,
  attrs ? 'discount'                       AS key_exists,
  attrs -> 'discount' IS NULL              AS sql_null,        -- key missing
  attrs -> 'discount' = 'null'::jsonb      AS json_null        -- key present, null value
FROM products;

SELECT name FROM products WHERE attrs -> 'tags' ? 'desk';

-- Rows where brand is Acme
SELECT name FROM products WHERE attrs @> '{"brand":"Acme"}';
 
-- Multiple conditions in one containment check
SELECT name FROM products WHERE attrs @> '{"brand":"Acme","in_stock":true}';
 
-- Nested containment works naturally
SELECT name FROM products WHERE attrs @> '{"specs":{"cpu":"M3"}}';
 
-- Array containment: does tags include "desk"?
SELECT name FROM products WHERE attrs @> '{"tags":["desk"]}';

SELECT '["a","b"]'::jsonb @> '"a"'::jsonb;      -- true
SELECT '[["a","b"]]'::jsonb @> '["a"]'::jsonb;  -- false

CREATE INDEX products_attrs_gin ON products USING GIN (attrs);

-- Uses the GIN index
EXPLAIN ANALYZE SELECT * FROM products WHERE attrs @> '{"brand":"Acme"}';
 
-- Does NOT use it — sequential scan
EXPLAIN ANALYZE SELECT * FROM products WHERE attrs ->> 'brand' = 'Acme';

CREATE INDEX products_attrs_path_gin
  ON products USING GIN (attrs jsonb_path_ops);

CREATE INDEX products_price_idx
  ON products (((attrs ->> 'price')::numeric));
 
-- Now this uses the index
EXPLAIN ANALYZE
SELECT name FROM products
WHERE (attrs ->> 'price')::numeric BETWEEN 100 AND 500
ORDER BY (attrs ->> 'price')::numeric;

-- Does any matching path exist?
SELECT name FROM products
WHERE attrs @? '$.specs.ram_gb ? (@ > 8)';
 
-- Does the path predicate evaluate to true?
SELECT name FROM products
WHERE attrs @@ '$.price > 300';
 
-- Filter inside arrays
SELECT name FROM products
WHERE attrs @? '$.tags[*] ? (@ == "desk")';

SELECT name, jsonb_path_query(attrs, '$.specs.*') AS spec_value
FROM products;
 
-- With a filter
SELECT name, jsonb_path_query_array(attrs, '$.tags[*] ? (@ starts with "d")')
FROM products;

-- Set or replace a nested value
UPDATE products
SET attrs = jsonb_set(attrs, '{specs,ram_gb}', '32')
WHERE name = 'Laptop Pro 14';
 
-- Fourth argument controls whether missing keys are created (default true)
UPDATE products
SET attrs = jsonb_set(attrs, '{warranty_years}', '2', true)
WHERE attrs ->> 'brand' = 'Acme';

UPDATE products
SET attrs = jsonb_set_lax(attrs, '{discount}', NULL, true, 'delete_key')
WHERE name = 'Monitor 27';

SELECT '{"a":{"x":1,"y":2}}'::jsonb || '{"a":{"x":9}}'::jsonb;
-- {"a": {"x": 9}}   -- y is gone

-- Delete one key
UPDATE products SET attrs = attrs - 'discount'
WHERE name = 'Monitor 27';
 
-- Delete several keys at once
UPDATE products SET attrs = attrs - array['in_stock','discount']
WHERE name = 'Desk Lamp';
 
-- Delete at a nested path
UPDATE products SET attrs = attrs #- '{specs,watts}'
WHERE name = 'Desk Lamp';
 
-- Drop the first element of an array
UPDATE products SET attrs = jsonb_set(attrs, '{tags}', (attrs -> 'tags') - 0)
WHERE name = 'Mechanical Kbd';

-- Object to key/value rows
SELECT p.name, kv.key, kv.value
FROM products p, jsonb_each(p.attrs -> 'specs') AS kv;
 
-- Values as text
SELECT p.name, kv.key, kv.value
FROM products p, jsonb_each_text(p.attrs -> 'specs') AS kv;
 
-- Array to rows
SELECT p.name, tag
FROM products p, jsonb_array_elements_text(p.attrs -> 'tags') AS tag;
 
-- Just the keys
SELECT DISTINCT k FROM products, jsonb_object_keys(attrs) AS k;

SELECT p.name, tag
FROM products p,
LATERAL jsonb_array_elements_text(
  CASE WHEN jsonb_typeof(p.attrs -> 'tags') = 'array'
       THEN p.attrs -> 'tags'
       ELSE '[]'::jsonb END
) AS tag;

SELECT jsonb_agg(jsonb_build_object('name', name, 'price', attrs -> 'price'))
FROM products;
 
SELECT jsonb_object_agg(name, attrs -> 'price') FROM products;
 
SELECT jsonb_pretty(jsonb_build_object(
  'count', count(*),
  'brands', jsonb_agg(DISTINCT attrs ->> 'brand')
))
FROM products;

ALTER TABLE products
  ADD COLUMN price numeric
  GENERATED ALWAYS AS ((attrs ->> 'price')::numeric) STORED;
 
CREATE INDEX ON products (price);