8 expert PostgreSQL Jsonb Path Query answers researched from official documentation. Every answer cites authoritative sources you can verify.
SQL/JSON Path Language (PostgreSQL 12+, SQL:2016 standard): Powerful JSONB querying using jsonb_path_query() functions - alternative to repetitive ->> operators. Basic syntax: SELECT jsonb_path_query(data, '$.user.addresses[*].city') FROM orders; extracts all cities from addresses array as set of JSONB values. More expressive than traditional -> operators for complex nested queries. Enables filtering, recursive descent, regex matching within JSON structures.
Advanced path patterns (2025): (1) Filters with predicates: $.products[] ? (@.price > 100 && @.stock > 0) finds products over $100 with stock, $.users[] ? (@.age >= 18 && @.verified == true) filters adults, (2) Recursive descent with depth limits: $.{2 to 5}.name searches nested objects 2-5 levels deep, $.{1 to 3}[] ? (@.type == "admin") finds admin objects up to 3 levels deep, (3) Parameterized queries (SQL injection prevention): jsonb_path_query(data, '$.items[] ? (@.category == $cat)', '{"cat": "electronics"}') uses variables instead of string concatenation, (4) Array operations with regex: $.tags[*] ? (@ like_regex "^(urgent|critical)" flag "i") case-insensitive regex matching on array elements.
Production recommendations (2025): (1) Use jsonb_path_exists() for WHERE clause filtering (boolean checks, GIN-indexable), (2) Use path queries for complex nested/array filtering with predicates, (3) Stick to -> and ->> for simple single-path extractions, (4) Use parameterized variables for dynamic queries (prevent SQL injection), (5) Enable strict mode in production to catch missing paths early. Real-world use case: E-commerce order filtering - Traditional: WHERE (data->'items'->0->>'price')::numeric > 1000 OR (data->'items'->1->>'price')::numeric > 1000 ... (unmaintainable for variable array sizes). Path query: WHERE jsonb_path_exists(data, '$.items[*] ? (@.price > 1000)') handles any array size. PostgreSQL versions: Path functions and strict/lax modes available 12+, regex improvements in 14+.
SQL/JSON Path Language (PostgreSQL 12+, SQL:2016 standard): Powerful JSONB querying using jsonb_path_query() functions - alternative to repetitive ->> operators. Basic syntax: SELECT jsonb_path_query(data, '$.user.addresses[*].city') FROM orders; extracts all cities from addresses array as set of JSONB values. More expressive than traditional -> operators for complex nested queries. Enables filtering, recursive descent, regex matching within JSON structures.
Advanced path patterns (2025): (1) Filters with predicates: $.products[] ? (@.price > 100 && @.stock > 0) finds products over $100 with stock, $.users[] ? (@.age >= 18 && @.verified == true) filters adults, (2) Recursive descent with depth limits: $.{2 to 5}.name searches nested objects 2-5 levels deep, $.{1 to 3}[] ? (@.type == "admin") finds admin objects up to 3 levels deep, (3) Parameterized queries (SQL injection prevention): jsonb_path_query(data, '$.items[] ? (@.category == $cat)', '{"cat": "electronics"}') uses variables instead of string concatenation, (4) Array operations with regex: $.tags[*] ? (@ like_regex "^(urgent|critical)" flag "i") case-insensitive regex matching on array elements.
Production recommendations (2025): (1) Use jsonb_path_exists() for WHERE clause filtering (boolean checks, GIN-indexable), (2) Use path queries for complex nested/array filtering with predicates, (3) Stick to -> and ->> for simple single-path extractions, (4) Use parameterized variables for dynamic queries (prevent SQL injection), (5) Enable strict mode in production to catch missing paths early. Real-world use case: E-commerce order filtering - Traditional: WHERE (data->'items'->0->>'price')::numeric > 1000 OR (data->'items'->1->>'price')::numeric > 1000 ... (unmaintainable for variable array sizes). Path query: WHERE jsonb_path_exists(data, '$.items[*] ? (@.price > 1000)') handles any array size. PostgreSQL versions: Path functions and strict/lax modes available 12+, regex improvements in 14+.
Performance optimization: (1) Use jsonb_path_exists() for boolean checks: WHERE jsonb_path_exists(data, '$.items[*] ? (@.price > 1000)') - returns boolean without constructing result set, GIN-indexable, optimal for WHERE clauses, (2) Combine with GIN indexes: CREATE INDEX idx_data ON products USING GIN (data jsonb_path_ops); enables indexed containment queries used by path expressions. Both @? (exists) and @@ (match) operators benefit from GIN indexes, (3) Strict vs lax mode: jsonb_path_query(data, 'strict $.user.email') throws error if path missing (strict mode), vs NULL return (lax mode default). Strict mode catches data quality issues early. Performance trade-off: Path expressions comparable to -> operators for complex queries (arrays, filters), but 20-40% slower for simple single-path extractions.
Performance optimization: (1) Use jsonb_path_exists() for boolean checks: WHERE jsonb_path_exists(data, '$.items[*] ? (@.price > 1000)') - returns boolean without constructing result set, GIN-indexable, optimal for WHERE clauses, (2) Combine with GIN indexes: CREATE INDEX idx_data ON products USING GIN (data jsonb_path_ops); enables indexed containment queries used by path expressions. Both @? (exists) and @@ (match) operators benefit from GIN indexes, (3) Strict vs lax mode: jsonb_path_query(data, 'strict $.user.email') throws error if path missing (strict mode), vs NULL return (lax mode default). Strict mode catches data quality issues early. Performance trade-off: Path expressions comparable to -> operators for complex queries (arrays, filters), but 20-40% slower for simple single-path extractions.