Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Drop table if exists  
DROP TABLE IF EXISTS my_table;  
  
-- Create table with jsonb_col  
CREATE TABLE my_table (  
    id SERIAL PRIMARY KEY,  
    jsonb_col JSONB  
);  
  
-- Insert test data  
INSERT INTO my_table (jsonb_col) VALUES  
('{"order": {"createdAt": "2025-08-10", "amount": 100}}'), 
('{"order": {"createdAt": "2025-08-11", "amount": 150}}'),  
('{"order": {"createdAt": "2025-08-12", "amount": 250}}'),  
('{"order": {"createdAt": "2025-08-13", "amount": 300}}'),  
('{"order": {"createdAt": "2025-08-14", "amount": 400}}');  

DROP TABLE

CREATE TABLE

INSERT 0 5

/*
You are misinformed.
CREATE INDEX my_index ON my_table USING GIN (jsonb_col);
SELECT * FROM my_table WHERE jsonb_col @? '$.order.createdAt >= "2025-08-12"';
*/

CREATE INDEX my_index ON my_table USING GIN (jsonb_col);
SELECT * FROM my_table WHERE jsonb_col @? '$.order.createdAt >= "2025-08-12"';

-- @? is jsonb_path_exists. What you want is @@ jsonb_path_match

SELECT * FROM my_table WHERE jsonb_col @@ '$.order.createdAt >= "2025-08-12"'

CREATE INDEX

id
jsonb_col

1
{"order": {"amount": 100, "createdAt": "2025-08-10"}}

2
{"order": {"amount": 150, "createdAt": "2025-08-11"}}

3
{"order": {"amount": 250, "createdAt": "2025-08-12"}}

4
{"order": {"amount": 300, "createdAt": "2025-08-13"}}

5
{"order": {"amount": 400, "createdAt": "2025-08-14"}}

SELECT 5

id
jsonb_col

3
{"order": {"amount": 250, "createdAt": "2025-08-12"}}

4
{"order": {"amount": 300, "createdAt": "2025-08-13"}}

5
{"order": {"amount": 400, "createdAt": "2025-08-14"}}

SELECT 3

-- try to get an index scan
set enable_seqscan to off;
  
-- Query using JSONPath  
explain (analyze, buffers)
SELECT * FROM my_table WHERE jsonb_col @@ '$.order.createdAt >= "2025-08-12"'

--> it reads all rows from the index (rows=5 ) 
--  to filter afterwards (Rows Removed by Index Recheck: 2)
--  because the GIN index doesn't support range scanes
-- ( despite showing it as Index Cond )

SET

QUERY PLAN

Bitmap Heap Scan on my_table  (cost=61.29..65.31 rows=1 width=36) (actual time=0.034..0.036 rows=3 loops=1)

  Recheck Cond: (jsonb_col @@ '($."order"."createdAt" >= "2025-08-12")'::jsonpath)

  Rows Removed by Index Recheck: 2

  Heap Blocks: exact=1

  Buffers: shared hit=4

  ->  Bitmap Index Scan on my_index  (cost=0.00..61.29 rows=1 width=0) (actual time=0.020..0.020 rows=5 loops=1)

        Index Cond: (jsonb_col @@ '($."order"."createdAt" >= "2025-08-12")'::jsonpath)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=17

Planning Time: 0.095 ms

Execution Time: 0.059 ms

EXPLAIN

-- Insert test data  with array
INSERT INTO my_table (jsonb_col) VALUES  
('{"order": [
  {"createdAt": "2025-08-15", "amount": 500},
  {"createdAt": "2025-08-16", "amount": 600}
]}')  ;

INSERT 0 1

/*
Alternatively you could just make a more specific (and faster) index just on the creation time.
CREATE INDEX my_index ON my_table ((jsonb_col #>> '{order,createdAt}')::timestamptz);
SELECT * FROM my_table WHERE (jsonb_col #>> '{order,createdAt}')::timestamptz > now() - '2 days'::interval;
Making a function for that lookup cleans things up nicely.
CREATE FUNCTION order_created(jsonb) RETURNS timestamptz IMMUTABLE STRICT LANGUAGE sql AS $$
 SELECT ($1 #>> '{order,createdAt}')::timestamptz;
$$;
Then your index and query look like:
CREATE INDEX my_index ON my_table (order_created(jsonb_col));
SELECT * FROM my_table WHERE order_created(jsonb_col) > now() - '2 days'::interval;
*/

CREATE FUNCTION order_created(jsonb) RETURNS timestamptz IMMUTABLE STRICT LANGUAGE sql AS $$
 SELECT ($1 #>> '{order,createdAt}')::timestamptz;
$$;
CREATE INDEX my_index2 ON my_table (order_created(jsonb_col));

CREATE FUNCTION

CREATE INDEX

SELECT * FROM my_table WHERE order_created(jsonb_col) > now() - '2 days'::interval;

--> it doesn't find the row with an array of orders
--  because you need a GIN index for that

id
jsonb_col

SELECT 0
id	jsonb_col
1	{"order": {"amount": 100, "createdAt": "2025-08-10"}}
2	{"order": {"amount": 150, "createdAt": "2025-08-11"}}
3	{"order": {"amount": 250, "createdAt": "2025-08-12"}}
4	{"order": {"amount": 300, "createdAt": "2025-08-13"}}
5	{"order": {"amount": 400, "createdAt": "2025-08-14"}}