JSONB Path Queries and Manipulation

PostgreSQL 12 introduced SQL/JSON path language via the jsonb_path_query and @? operators, providing a much more expressive way to navigate and filter JSONB structures compared to the basic operators. JSON path expressions use a dollar sign ($) for the root, dot notation for key access, bracket notation for array indexing, [*] to iterate over all array elements, and filter expressions in ? () notation. The jsonb_set function modifies a specific path in a JSONB value non-destructively, returning a new JSONB value. jsonb_insert adds array elements. The concatenation operator || merges two JSONB objects (right-side keys overwrite). The deletion operator - removes a key or array element, and #- removes a nested path.

Example

-- jsonb_path_query — SQL/JSON path language
SELECT jsonb_path_query(
  '{"store":{"books":[{"title":"DB Design","price":29},{"title":"SQL","price":39}]}}',
  '$.store.books[*].title'
);
-- Returns: "DB Design", "SQL"

-- Filter in path expression
SELECT jsonb_path_query(
  '{"items":[{"sku":"A","qty":5},{"sku":"B","qty":1}]}',
  '$.items[*] ? (@.qty > 2)'
);

-- @? — check if path exists (returns boolean)
SELECT payload @? '$.items[*] ? (@.qty > 2)' FROM events;

-- jsonb_set — update a nested value
UPDATE events
  SET payload = jsonb_set(payload, '{total}', '200.00')
WHERE id = 1;

-- || — merge/overwrite keys
UPDATE users
  SET preferences = preferences || '{"theme":"dark","lang":"fr"}'::jsonb
WHERE id = 42;

-- - operator — remove a key
UPDATE events
  SET payload = payload - 'referral'
WHERE event_type = 'signup';

-- #- — remove a nested path
UPDATE events
  SET payload = payload #- '{items,0}'  -- remove first array element
WHERE id = 1;

-- jsonb_strip_nulls — remove null-valued keys
SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":3}');  -- {"a":1,"c":3}

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned