How to Use Oracle-Style JSON Paths in PostgreSQL

How do I run Oracle-style JSON path queries in PostgreSQL?

Leverage Oracle-style JSON path expressions with PostgreSQL’s jsonb_path_query* functions to extract, filter, and manipulate JSON data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is Oracle-style JSON path syntax?

Oracle uses dot-notation (e.g., $.orders[?(@.total>100)]) to navigate JSON. PostgreSQL 12+ supports the same syntax through jsonb_path_* functions when you set the parameter jsonpath_legacy_oracle to on.

How do I enable Oracle compatibility?

Set the session parameter: SET jsonpath_legacy_oracle TO on;. This lets you pass Oracle JSON paths directly to jsonb_path_query, jsonb_path_exists, and friends.

How do I extract JSON arrays?

Use jsonb_path_query with a path filter.Example: list high-value orders per customer: $.orders[?(@.total_amount > 1000)].

Step-by-step example

1. Enable Oracle mode.
2. Join Customers and Orders.
3. Apply jsonb_path_query to the order_data JSONB column.

How do I test if a value exists?

Use jsonb_path_exists. Oracle path $.stock ? (@ < 5) returns TRUE if any product’s stock is under five.

Can I update JSON with Oracle paths?

Yes, with jsonb_set.Compute the path text dynamically, then supply a new value.

Best practices for Oracle paths

• Turn the parameter on only per-session to avoid surprises.
• Keep paths short; long filters slow execution.
• Create GIN indexes on JSONB columns to speed jsonb_path_* calls.

When should I avoid Oracle mode?

If your team already writes PostgreSQL JSONPath, mixing styles causes confusion. Standard JSONPath is ISO/IEC compliant; prefer it for new code.

Why How to Use Oracle-Style JSON Paths in PostgreSQL is important

How to Use Oracle-Style JSON Paths in PostgreSQL Example Usage


-- List customers with an order over $1000 using Oracle JSON path
SET jsonpath_legacy_oracle TO on;
SELECT DISTINCT c.id, c.name, o.id AS order_id, o.total_amount
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
WHERE  jsonb_path_exists(o.extra_info, '$.items[?(@.total_amount > 1000)]');

How to Use Oracle-Style JSON Paths in PostgreSQL Syntax


-- Enable Oracle-style JSON paths
SET jsonpath_legacy_oracle TO on;

-- Extract high-value orders (> $500)
SELECT c.id, c.name,
       jsonb_path_query(o.order_data, '$.items[?(@.total_amount > 500)]') AS big_items
FROM   Customers c
JOIN   Orders   o ON o.customer_id = c.id;

-- Check if any product is out of stock
SELECT jsonb_path_exists(p.specs, '$.stock ? (@ = 0)') AS out_of_stock
FROM   Products p;

Common Mistakes

Leaving jsonpath_legacy_oracle ON globally: Setting it at the database level forces all sessions into Oracle mode, surprising users who expect standard JSONPath. Fix by issuing SET only in the session needing Oracle paths.
Forgetting to cast text to JSONB: jsonb_path_query requires a JSONB input. Passing a TEXT column without casting raises an error. Fix by using column::jsonb or storing data as JSONB.