JSONB in PostgreSQL: querying and indexing

When I hear JSON, the first things that come to mind are API responses and configuration files. Lately I have been using it somewhere else: inside relational databases, and specifically PostgreSQL.

A relational database earns its keep because the shape of the data is known in advance. Columns are typed, constraints hold, and the planner can reason about what you store. But some parts of a model refuse to sit still. Product attributes are the usual example: a keyboard has a layout and a switch type, a monitor has a panel type and a refresh rate, and a cable has neither.

You could model that with a column per attribute:

CREATE TABLE products (
    id       uuid PRIMARY KEY,
    name     varchar(200)   NOT NULL,
    price    numeric(10,2)  NOT NULL,
    color    varchar(50),
    wireless boolean,
    layout   varchar(20),
    -- ...and one more every time the catalog grows
);

Adding a column each time the structure changes gets tedious, and it leaves a table full of columns that are null for most rows. A JSON column handles the moving parts instead:

CREATE TABLE products (
    id      uuid          PRIMARY KEY,
    name    varchar(200)  NOT NULL,
    price   numeric(10,2) NOT NULL,
    details jsonb         NOT NULL
);

id, name and price stay relational, because every product has them and you sort, filter and join on them. Everything variable moves into details.

You keep relationships, transactions, constraints, joins and ordinary relational queries on the stable columns, and gain flexibility for the parts that do not fit a fixed schema. The point is that JSON complements relational modeling rather than replacing it. If an attribute is on every row and you query it constantly, it deserves a column.

PostgreSQL offers two types for this: json and jsonb.

JSON vs JSONB

Both store JSON. They differ in how PostgreSQL keeps it and what it can do with it afterwards.

Feature	`json`	`jsonb`
Storage	The original JSON text	Decomposed binary format
Insert speed	Faster	Slower; input is converted to a binary tree
Read/query speed	Slower; text is reparsed each time	Faster; already parsed
Indexing	Effectively none	Full support, including GIN
Whitespace	Preserved	Discarded
Key order	Preserved	Not preserved
Duplicate keys	All kept	Only the last one kept
Best fit	Preserving the exact original document	Data you query, filter or index
Typical choice	Rare	The default recommendation

The normalization is easy to see. Both values below come from the same input text:

SELECT '{"b": 1,   "a": 2,  "a": 3}'::json  AS as_json,
       '{"b": 1,   "a": 2,  "a": 3}'::jsonb AS as_jsonb;

           as_json           |     as_jsonb
-----------------------------+------------------
 {"b": 1,   "a": 2,  "a": 3} | {"a": 3, "b": 1}

json hands back exactly what you gave it. jsonb drops the extra whitespace, reorders the keys, and keeps only the last a. Worth knowing: the key order is not alphabetical. PostgreSQL sorts by key length first, then alphabetically, which is why a stored document comes back as color, layout, features, warranty, wireless.

That normalization costs a little space. Storing the same 500,000 documents in each type:

 json_size | jsonb_size
-----------+------------
 93 MB     | 106 MB

About 14% more for jsonb, which buys the binary layout that makes querying and indexing possible. json has to reparse the text on every access; jsonb does that work once, at write time.

For typical application use, jsonb is the better default, and it is what the PostgreSQL documentation recommends. Reach for json only when you must reproduce the original document byte for byte, such as storing a signed webhook payload whose signature covers the exact text.

Using JSONB in .NET

You need two things: a running PostgreSQL instance, and the EF Core packages.

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.12" />

Then add a connection string and register the DbContext:

builder.Services.AddDbContext<BlogDbContext>(options => options.UseNpgsql(
    builder.Configuration.GetConnectionString("BlogDatabase")
    ?? throw new InvalidOperationException("Configure ConnectionStrings:BlogDatabase.")));

Defining the model

The entity holds the relational columns plus one property for the flexible part. That property is a complex type, not a related entity.

public sealed class Product
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public ProductDetails Details { get; set; } = new();
}

public sealed class ProductDetails
{
    public string Color { get; set; } = "";
    public bool Wireless { get; set; }
    public string Layout { get; set; } = "";
    public List<string> Features { get; set; } = [];
    public Warranty Warranty { get; set; } = new();
}

public sealed class Warranty
{
    public int Months { get; set; }
    public string Provider { get; set; } = "";
}

ProductDetails covers all three shapes worth testing: scalars, an array, and a nested object.

Mapping to a jsonb column

Since EF Core 10, you map a complex type to a JSON column by calling ToJson() on it in OnModelCreating:

product.ComplexProperty(p => p.Details, details =>
{
    details.ToJson("details");
    details.Property(d => d.Color).HasJsonPropertyName("color");
    details.Property(d => d.Wireless).HasJsonPropertyName("wireless");
    details.Property(d => d.Layout).HasJsonPropertyName("layout");
    details.PrimitiveCollection(d => d.Features).HasJsonPropertyName("features");
    details.ComplexProperty(d => d.Warranty, warranty =>
    {
        warranty.HasJsonPropertyName("warranty");
        warranty.Property(w => w.Months).HasJsonPropertyName("months");
        warranty.Property(w => w.Provider).HasJsonPropertyName("provider");
    });
});

The explicit HasJsonPropertyName calls are optional but worth the keystrokes. They pin the names used inside the document, so changing your HTTP serialization settings later cannot quietly rename keys that your SQL and indexes depend on.

ToJson() is not a serializer. It configures EF Core’s model so EF knows the complex type and its members live in one JSON document. Storage type and SQL translation are the provider’s job. With Npgsql, that document becomes jsonb by default, so no EnableDynamicJson call and no manual serialization are needed.

The generated migration confirms it:

migrationBuilder.CreateTable(
    name: "products",
    columns: table => new
    {
        id = table.Column<Guid>(type: "uuid", nullable: false),
        name = table.Column<string>(type: "character varying(200)", maxLength: 200, nullable: false),
        price = table.Column<decimal>(type: "numeric(10,2)", precision: 10, scale: 2, nullable: false),
        details = table.Column<string>(type: "jsonb", nullable: false)
    },
    constraints: table => { table.PrimaryKey("PK_products", x => x.id); });

One jsonb column. No side table for ProductDetails, and none for the nested Warranty either.

Storing and reading

From here the entity behaves like any other:

app.MapPost("/products", async (Product product, BlogDbContext db, CancellationToken ct) =>
{
    db.Products.Add(product);
    await db.SaveChangesAsync(ct);
    return Results.Created($"/products/{product.Id}", product);
});

EF writes the whole object as a single parameter:

INSERT INTO products (details, id, name, price)
VALUES (@p0, @p1, @p2, @p3);

What actually gets stored

POST this:

{
  "name": "Aurora 75 Wireless",
  "price": 129.00,
  "details": {
    "color": "black", "wireless": true, "layout": "75%",
    "features": ["rgb", "hot-swappable"],
    "warranty": { "months": 24, "provider": "Acme" }
  }
}

and PostgreSQL reports a real JSON object, not an escaped string:

SELECT pg_typeof(details) AS db_type, jsonb_typeof(details) AS json_type
FROM products WHERE name = 'Aurora 75 Wireless';

 db_type | json_type
---------+-----------
 jsonb   | object

SELECT jsonb_pretty(details) FROM products WHERE name = 'Aurora 75 Wireless';

{
    "color": "black",
    "layout": "75%",
    "features": [
        "rgb",
        "hot-swappable"
    ],
    "warranty": {
        "months": 24,
        "provider": "Acme"
    },
    "wireless": true
}

Booleans are booleans, numbers are numbers, the array is an array, and the nested object survives. The keys came back reordered, because jsonb does not preserve input order.

Querying

You write ordinary LINQ. Npgsql turns member access on the complex type into the matching PostgreSQL JSON operator, and the filter runs in the database rather than in your process.

A top-level property:

db.Products.Where(p => p.Details.Color == "black")

SELECT p.id, p.name, p.price, p.details
FROM products AS p
WHERE (p.details ->> 'color') = 'black'

A nested property reads the same in C#:

db.Products.Where(p => p.Details.Warranty.Months >= 24)

but the SQL is not the same. EF does not chain ->; it uses the path operator and casts back to the CLR type:

SELECT p.id, p.name, p.price, p.details
FROM products AS p
WHERE (CAST(p.details #>> '{warranty,months}' AS integer)) >= 24

Collections change the SQL more noticeably:

db.Products.Where(p => p.Details.Features.Contains("hot-swappable"))

SELECT p.id, p.name, p.price, p.details
FROM products AS p
WHERE (p.details -> 'features') @> to_jsonb('hot-swappable'::text)

Worth noting how different that is from the way EF Core translates Contains against a relational column, where the same method turns into an IN list or a join. Here it becomes a containment check the GIN index can serve.

Compose them and you get all three in one statement:

SELECT p.id, p.name, p.price, p.details
FROM products AS p
WHERE (p.details ->> 'color') = @color
  AND (p.details -> 'features') @> to_jsonb(@feature)
  AND (CAST(p.details #>> '{warranty,months}' AS integer)) >= @months
ORDER BY p.name
LIMIT @p

These all rest on PostgreSQL’s JSON operators:

-> returns a JSON value, so you can keep drilling: details -> 'warranty' -> 'provider' gives "Acme", quotes included.
->> returns the value as text, which is what you compare against a string: details ->> 'color' = 'black'.
#>> follows a path and returns text: details #>> '{warranty,months}'.
@> asks whether one document contains another: details @> '{"color": "black"}'. This is the operator GIN indexes serve.

Using EF Core you rarely write them by hand. It is still worth recognizing them, because which operator ends up in the query decides which index can help.

Indexing

Efficient reads need an index that matches how you actually query. Without one, PostgreSQL reads everything.

All measurements below come from a table of 500,000 products on PostgreSQL 18.

Start with a selective containment query and no index:

EXPLAIN ANALYZE
SELECT id, name FROM products
WHERE details @> '{"features": ["limited-edition"]}';

 Gather  (cost=1000.00..18757.27 rows=1 width=37) (actual time=54.743..57.902 rows=50.00 loops=1)
   ->  Parallel Seq Scan on products  (cost=0.00..17757.17 rows=1 width=37)
         Filter: (details @> '{"features": ["limited-edition"]}'::jsonb)
         Rows Removed by Filter: 166650
 Execution Time: 57.946 ms

Half a million rows scanned to return 50.

GIN indexes

The usual index type for jsonb is GIN, a Generalized Inverted Index. Creating one is a one-liner:

CREATE INDEX ix_products_details ON products USING GIN (details);

It does not index the column as a single value. It breaks each document into searchable pieces and records which rows contain them. This document:

{ "color": "black", "wireless": true, "layout": "75%" }

becomes entries along these lines:

color    -> row 1, row 4, row 8
black    -> row 1, row 8
wireless -> row 1, row 2, row 5
true     -> row 1, row 5
layout   -> row 1, row 3
75%      -> row 1

Same query again:

 Bitmap Heap Scan on products  (cost=302.06..306.07 rows=1 width=37) (actual time=0.060..0.077 rows=50.00 loops=1)
   Recheck Cond: (details @> '{"features": ["limited-edition"]}'::jsonb)
   Heap Blocks: exact=3
   ->  Bitmap Index Scan on ix_products_details  (actual time=0.052..0.052 rows=50.00 loops=1)
 Execution Time: 0.089 ms

57.9 ms down to 0.089 ms, roughly 650x, because the query is selective: 50 rows out of 500,000.

Selectivity is the whole story, and it is where the “add an index and it gets fast” reflex breaks down. Run a query that matches 10% of the table and the same index behaves very differently:

EXPLAIN ANALYZE
SELECT id, name FROM products
WHERE details @> '{"color": "black", "wireless": true}';

 Bitmap Heap Scan on products  (actual time=19.993..105.864 rows=50000.00 loops=1)
   Heap Blocks: exact=15151
 Execution Time: 107.568 ms

Against 114.2 ms for the sequential scan. PostgreSQL used the index and gained almost nothing, because matching 50,000 rows means visiting 15,151 heap blocks either way. Finding the rows was never the expensive part.

Creating an index also does not oblige the planner to use it. It picks the plan it costs as cheapest, and for an unselective predicate a sequential scan often wins outright.

jsonb_ops and jsonb_path_ops

The default operator class is jsonb_ops, which indexes keys and values separately. The alternative is jsonb_path_ops, which indexes hashes of full paths:

CREATE INDEX ix_products_details ON products USING GIN (details jsonb_path_ops);

Feature	`jsonb_ops`	`jsonb_path_ops`
Default	Yes	No
Index entries	Separate entries for keys and values	Hashed full path plus value
Supported operators	`@>`, `?`, `?&`, `?\|`, `@?`, `@@`	`@>`, `@?`, `@@`
Key-existence queries (`?`)	Supported	Not supported
Index size (500k rows)	43 MB	31 MB
Best fit	Mixed JSON operators	Containment and JSONPath

The size difference is real: 43 MB against 31 MB for the same data, roughly 28% smaller, because hashing one path per value beats storing every key and value separately.

The cost is narrower coverage. With only jsonb_path_ops in place, a key-existence check gets no help:

EXPLAIN SELECT count(*) FROM products WHERE details ? 'sku';

 ->  Parallel Seq Scan on products  (cost=0.00..19270.17 rows=208312 width=0)
       Filter: (details ? 'sku'::text)

Start with jsonb_ops. Move to jsonb_path_ops when your queries are mostly @> or JSONPath, you never need key-existence operators, and index size is worth optimizing.

Indexing one property

GIN covers containment. It does nothing for ->> comparisons, which is easy to miss. With a full GIN index on details in place:

EXPLAIN ANALYZE SELECT id, name FROM products WHERE details ->> 'sku' = 'KB-421337';

 Gather  (cost=1000.00..21041.00 rows=2500 width=37) (actual time=27.345..33.855 rows=1.00 loops=1)
   ->  Parallel Seq Scan on products
         Filter: ((details ->> 'sku'::text) = 'KB-421337'::text)
 Execution Time: 33.895 ms

A sequential scan to find one row, index or not. The GIN index cannot serve this query because ->> is not one of its operators.

An expression index matches how the query is written:

CREATE INDEX ix_products_details_sku ON products ((details ->> 'sku'));

 Index Scan using ix_products_details_sku on products  (actual time=0.046..0.046 rows=1.00 loops=1)
   Index Cond: ((details ->> 'sku'::text) = 'KB-421337'::text)
 Execution Time: 0.067 ms

33.9 ms to 0.067 ms, from an index of 15 MB against GIN’s 43 MB. The catch is that it indexes exactly one expression. Query details ->> 'color' and it is useless; you would need another index.

Trade-offs

Indexes are not free. Each one takes disk space, adds work to every insert and update, and must be maintained when the indexed values change. JSON documents hold many keys, so a broad GIN index grows fast. For this dataset:

Object	Size
`products` table	130 MB
GIN, `jsonb_ops`	43 MB
GIN, `jsonb_path_ops`	31 MB
Expression index on one property	15 MB

A rule of thumb:

Many different properties and containment queries: GIN.
One property queried constantly, especially a selective one: expression index.
Queries that return a large share of the table: an index will not save you; fix the query or the model.
No measured performance problem: no index.

Measure with EXPLAIN ANALYZE on realistic data volumes before adding anything. On a thousand rows every plan looks fine.

JSONB in PostgreSQL: querying, indexing, and EF Core mapping