PostgreSQL JSONB GIN indexes can narrow JSONPath searches using extractable path-to-constant equality tests. Range comparisons, inequality, and bare path-existence tests do not supply those search keys by themselves. Mixed predicates need closer inspection.

This guide helps a developer working in Terminal on a Mac choose an index for filtering synthetic article rows, pass the query through node-postgres, and inspect the resulting plan. The core extraction rule is documented in PostgreSQL’s JSONB indexing reference.

Which predicate shapes supply useful GIN search keys?

For @? and @@, PostgreSQL extracts equality clauses connecting an accessor chain to a constant. Supported accessors include named members, array wildcards, and array indexes. Both JSONB GIN operator classes support these forms; jsonb_ops additionally supports object wildcards and recursive descent. JSON Types.

JSONPath expression Useful extracted search condition?
$.category == "layout" Yes, with either operator class
$.tags[*] == "grid" Yes, with either operator class
$.tags[0] == "grid" Yes, with either operator class; the complete predicate still needs checking
$.* == "layout" Supported by jsonb_ops, not jsonb_path_ops
$.**.category == "layout" Supported by jsonb_ops, not jsonb_path_ops

The following boundaries follow from PostgreSQL 18’s extraction implementation. Here, “none” means no useful extracted condition from that expression alone, rather than a promise about the plan’s scan-node name. PostgreSQL 18 JSONB GIN implementation.

JSONPath expression Extracted condition
$.words > 1200 None
$.category != "layout" None
exists($.category) None
$.title like_regex "Grid" None
$.words == $.target_words None: neither operand is a scalar constant

Why do AND and OR behave differently?

With $.category == "layout" && $.words > 1200, the equality can select candidates; the complete condition is rechecked. With $.category == "layout" || $.words > 1200, using only the equality would miss rows matching the range. Extraction therefore fails for that OR when either arm is unsupported. An OR joining two supported equalities can supply search conditions. These rules concern extraction inside one JSONPath expression; other SQL conditions can still affect the overall plan. PostgreSQL 18 JSONB GIN implementation.

Which operator and operator class should you choose?

Use @? to ask whether a path returns any item, typically through a filter. Use @@ for a Boolean predicate check. A bare Boolean comparison passed to @? is a trap: even a false Boolean is a returned item. PostgreSQL explicitly distinguishes these forms in JSON Functions and Operators.

These two queries express the same task for the sample article structure introduced below:

SELECT id
FROM jsonpath_article_demo
WHERE doc @? '$.tags[*] ? (@ == "grid")';

SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.tags[*] == "grid"';

Choose jsonb_path_ops for the demonstrated named-path equality workload. Choose the default jsonb_ops when you also need SQL key-existence operators such as ?, or the wildcard accessors described above. Both support @>, @?, and @@; operator support alone does not make every predicate selective. JSONB indexing.

The examples target PostgreSQL 18, the version currently identified by the retrieved official documentation. SQL/JSON path support arrived in PostgreSQL 12, so these operators are unavailable on earlier servers. PostgreSQL 12 is now listed as unsupported; its release notes establish the introduction boundary, not a recommended installation version. PostgreSQL 12 release notes.

How can you reproduce the query on a Mac?

Start with a running PostgreSQL 18 database, the psql command available in Terminal, and an existing scratch database selected through your usual connection settings. This exercise creates a new table and does not replace an existing one. If that table name already exists, use another scratch database or consistently rename the demonstration table.

Open psql in Terminal, run SHOW server_version;, and then paste this complete setup. It creates 20,000 synthetic article rows: every hundredth article has category layout, and word counts cycle from 400 through 1,999.

CREATE TABLE jsonpath_article_demo (
    id integer PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO jsonpath_article_demo (id, doc)
SELECT
    n,
    jsonb_build_object(
        'title', 'Article ' || n,
        'category', CASE
            WHEN n % 100 = 0 THEN 'layout'
            ELSE 'notes'
        END,
        'words', 400 + n % 1600,
        'tags', jsonb_build_array(
            CASE WHEN n % 100 = 0 THEN 'grid' ELSE 'text' END
        )
    )
FROM generate_series(1, 20000) AS sample(n);

CREATE INDEX jsonpath_article_demo_doc_gin
ON jsonpath_article_demo USING gin (doc jsonb_path_ops);

ANALYZE jsonpath_article_demo;

SELECT id, doc->>'title' AS title
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200'
ORDER BY id
LIMIT 5;

The derived first five IDs are 900, 1000, 1100, 1200, and 1300; the full condition matches 84 generated rows. Those are illustrative dataset results, not performance measurements. The arithmetic and expected IDs were checked with a small JavaScript calculation; PostgreSQL execution was unavailable in the research environment.

ANALYZE collects table statistics that the planner uses when choosing execution plans. Running it after the load gives the demonstration a more useful planning baseline. ANALYZE.

How do you tell whether GIN helped?

After the setup, run these two complete diagnostic queries in the same database. The first includes an extractable equality; the second isolates the range predicate.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.words > 1200';

For the first query, look for a Bitmap Index Scan naming jsonpath_article_demo_doc_gin, usually beneath a Bitmap Heap Scan. Read Index Cond, Recheck Cond, actual row counts, buffer activity, and execution time together. An index condition displaying the complete JSONPath does not mean every comparison became an index search key. PostgreSQL’s Using EXPLAIN explains these plan fields and the distinction between candidate retrieval and subsequent filtering.

A sequential scan does not by itself disprove index eligibility. The planner chooses among estimated costs, and a small table or a broad match can favor scanning. Conversely, merely seeing an index node does not establish useful narrowing. Compare the work performed and the elapsed time under comparable conditions. EXPLAIN ANALYZE actually executes each query. Using EXPLAIN.

For this exercise, the practical question is whether the category equality reduces the candidates enough to make evaluating the word-count condition inexpensive. The generated category distribution provides a clear test case, but it cannot establish the best plan for a differently distributed article collection.

How should node-postgres pass the JSONPath?

Pass the complete JSONPath as one SQL parameter and cast it to jsonpath. The SQL placeholder $1 and the JSONPath root $ belong to different languages. node-postgres accepts query text plus a values array; the server receives the supplied path as the parameter value. Queries.

With a current supported Node.js installation, run npm install pg in a working directory. Save the following complete program as article-query.mjs. It requires the SQL setup above in the same database. The package installation command comes from the node-postgres introduction.

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const category = 'layout';
const path = `$.category == ${JSON.stringify(category)} && $.words > 1200`;

try {
  await client.connect();

  const result = await client.query({
    text: `
      SELECT id, doc->>'title' AS title
      FROM jsonpath_article_demo
      WHERE doc @@ $1::jsonpath
      ORDER BY id
      LIMIT 5
    `,
    values: [path],
  });

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Run node article-query.mjs with the database connection environment configured for the scratch database. Client reads PostgreSQL connection variables such as PGHOST, PGPORT, PGDATABASE, and PGUSER. Connection failures and a missing demonstration table are reported by the program. Connecting.

Here, JSON.stringify(category) constructs the JSONPath string literal. The resulting path still contains the equality against the literal "layout"; passing that path through a SQL parameter does not replace the equality with a JSONPath variable.

If you use jsonb_path_exists with its vars argument instead, you are calling a function with named JSONPath variables. That is a documented evaluation interface, but it is not the indexed @? operator form. For this GIN lookup, keep the operator query shown above. JSON Functions and Operators, JSONB indexing.

What should you use when the main condition is a numeric range?

If selecting articles by word count is the main task, expose that scalar through a B-tree expression index. PostgreSQL documents B-tree support for equality and ordered range comparisons, and expression indexes allow indexing a computed value. Index Types, Indexes on Expressions.

Run this additional setup and query against the generated table:

CREATE INDEX jsonpath_article_demo_words_btree
ON jsonpath_article_demo (((doc->>'words')::integer));

ANALYZE jsonpath_article_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE (doc->>'words')::integer > 1200;

The query deliberately uses the same expression as the index. Its prerequisite is a single integer-compatible words value per article. The generated rows satisfy that condition; arbitrary text or out-of-range values can make the cast fail during index creation or later writes. This rewrite is not a general replacement for JSONPath comparisons over arrays, mixed types, or multiple matching values.

Use that boundary as your decision aid: start with GIN when selective document equalities drive the lookup, and consider a B-tree expression index when a consistently typed scalar range drives it. Preserve the intended result set first, then use the actual plan to decide whether the chosen index earns its maintenance cost.

Sources