JSONB 指南

JSONB 运算符

用于提取和导航 JSONB 值的核心运算符。

-- Sample table
CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  data JSONB
);
INSERT INTO products (data) VALUES
  ('{"name":"Laptop","price":999,"tags":["tech","sale"],"specs":{"ram":16}}');

-- -> returns JSONB
SELECT data->'name' FROM products;          -- "Laptop"
SELECT data->'specs'->'ram' FROM products;  -- 16

-- ->> returns text
SELECT data->>'name' FROM products;         -- Laptop

-- #> path operator (JSONB)
SELECT data #> '{specs,ram}' FROM products;   -- 16

-- #>> path operator (text)
SELECT data #>> '{specs,ram}' FROM products;  -- 16

-- @> containment: does left contain right?
SELECT * FROM products WHERE data @> '{"tags":["sale"]}';

-- ? key exists
SELECT * FROM products WHERE data ? 'price';

-- ?| any key exists
SELECT * FROM products WHERE data ?| ARRAY['price','sku'];

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

修改 JSONB

-- jsonb_set: update a path
UPDATE products
SET data = jsonb_set(data, '{price}', '1199', false)
WHERE id = 1;

-- Concatenate / merge with ||
UPDATE products
SET data = data || '{"currency":"USD"}'
WHERE id = 1;

-- Remove a key with -
UPDATE products SET data = data - 'tags' WHERE id = 1;

-- Remove nested key with #-
UPDATE products SET data = data #- '{specs,ram}' WHERE id = 1;

-- jsonb_insert: insert into array
UPDATE products
SET data = jsonb_insert(data, '{tags,0}', '"featured"')
WHERE id = 1;

GIN 索引

-- Default GIN index (supports @>, ?, ?|, ?&)
CREATE INDEX idx_products_data ON products USING GIN (data);

-- jsonb_path_ops GIN (only supports @>, faster)
CREATE INDEX idx_products_data_path ON products
  USING GIN (data jsonb_path_ops);

-- Index a specific key (for range queries on extracted value)
CREATE INDEX idx_products_price
  ON products ((data->>'price')::numeric);

-- JSON path query (PostgreSQL 12+)
SELECT * FROM products
WHERE jsonb_path_exists(data, '$.tags[*] ? (@ == "sale")');

jsonb_agg 与 jsonb_build_object

-- jsonb_agg: aggregate rows into a JSON array
SELECT
  user_id,
  jsonb_agg(
    jsonb_build_object('id', id, 'total', total, 'date', created_at)
    ORDER BY created_at
  ) AS orders
FROM orders
GROUP BY user_id;

-- jsonb_object_agg: build object from key-value pairs
SELECT jsonb_object_agg(code, name) AS country_map
FROM countries;

-- jsonb_build_array
SELECT jsonb_build_array(1, 'two', true, NULL);

-- jsonb_each / jsonb_each_text: expand to rows
SELECT key, value FROM jsonb_each('{"a":1,"b":2}'::jsonb);

-- jsonb_array_elements: expand array to rows
SELECT elem FROM jsonb_array_elements('[1,2,3]'::jsonb) AS elem;

-- jsonb_to_record / jsonb_to_recordset
SELECT * FROM jsonb_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]'::jsonb)
AS t(id int, name text);

运算符快速参考