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

> Use jsonb, not json, for flexible PostgreSQL columns. EF Core 10 maps it with ToJson(); GIN indexes serve containment, expression indexes serve one key.

- Author: Pavle Davitkovic
- Published: 2026-09-23
- Topic: Databases
- Canonical: https://pavle.codes/blog/jsonb-in-postgresql/

`jsonb` is PostgreSQL's binary JSON column type. It stores fields that vary from row to row, such as product attributes, next to normal typed columns, and you can still query and index them.

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:

```sql
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:

```sql
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. 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:

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

```text
           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:

```text
 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.

```xml
<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`:

```csharp
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](https://learn.microsoft.com/en-us/ef/core/modeling/complex-types), not a related entity.

```csharp
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`:

```csharp
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:

```csharp
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:

```csharp
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:

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

### What actually gets stored

POST this:

```json
{
  "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:

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

```text
 db_type | json_type
---------+-----------
 jsonb   | object
```

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

```json
{
    "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:

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

```sql
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#:

```csharp
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:

```sql
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:

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

```sql
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](https://pavle.codes/blog/where-bulk-contains/#how-ef-core-translates-contains), 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:

```sql
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](https://www.postgresql.org/docs/current/functions-json.html):

- `->` 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:

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

```text
 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:

```sql
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:

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

becomes entries along these lines:

```text
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:

```text
 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 decides the outcome, 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:

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

```text
 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:

```sql
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 | `@>`, `?`, `?&`, <code>?&#124;</code>, `@?`, `@@` | `@>`, `@?`, `@@` |
| 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:

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

```text
 ->  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:

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

```text
 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:

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

```text
 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.
