JSON Support

This page documents how the Npgsql Entity Framework Core Provider (EF Core PG) enables working with JSON data in PostgreSQL. PostgreSQL offers robust support for JSON data with its json and jsonb data types, and the Npgsql EF Core Provider allows developers to seamlessly interact with this data through Entity Framework Core's LINQ query system.

Overview

PostgreSQL provides two JSON data types:

1. json - Stores JSON data as text, preserving formatting and order.
2. jsonb - Stores JSON in a binary format, optimized for querying and providing indexing support.

The Npgsql EF Core Provider supports several approaches for working with JSON data, ranging from raw strings to strongly-typed POCOs and the System.Text.Json DOM.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlTypeMappingSource.cs89-99 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs14-19 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonDomTranslator.cs14-19

JSON Mapping Approaches

1. POCO Objects to JSON

The POCO mapping approach allows you to map Plain Old CLR Objects (POCOs) directly to JSON columns. The provider automatically handles serialization and deserialization using System.Text.Json.

Queries can directly reference POCO properties, which are translated to JSON path navigation:

Sources: test/EFCore.PG.FunctionalTests/Query/JsonPocoQueryTest.cs123-138 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs67-71

2. System.Text.Json DOM Types

You can use JsonDocument or JsonElement for semi-structured data where the schema might vary.

CLR Type	PostgreSQL Type	Internal Mapping Class
JsonDocument	jsonb / json	NpgsqlJsonTypeMapping
JsonElement	jsonb / json	NpgsqlJsonTypeMapping

Sources: src/EFCore.PG/Storage/Internal/NpgsqlTypeMappingSource.cs96-99 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonDomTranslator.cs64-76

3. EF Core Owned Entities (JSON Columns)

The provider supports the standard EF Core "JSON Columns" feature where owned entities are stored in a single JSON column rather than separate tables.

• NpgsqlStructuralJsonTypeMapping handles the mapping for these owned structures.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlTypeMappingSource.cs90-91

Query Translation and Operators

The provider translates LINQ expressions into PostgreSQL-specific JSON operators.

JSON Path Access

The translation system converts property access and JsonElement.GetProperty() calls into path navigation.

.NET Expression	SQL Translation	Operator Description
e.Customer.Name	j."Customer" ->> 'Name'	Get field as text
e.Customer.Address.Zip	j."Customer" #>> '{Address,Zip}'	Get path as text
e.Customer.Orders[0]	j."Customer" -> 'Orders' -> 0	Get array element

Sources: test/EFCore.PG.FunctionalTests/Query/JsonPocoQueryTest.cs91-101 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs85-94 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonDomTranslator.cs78-83

Type Casting

Since PostgreSQL JSON operators (->>, #>>) return text, the provider automatically adds CAST expressions when comparing against non-string types.

Sources: test/EFCore.PG.FunctionalTests/Query/JsonPocoQueryTest.cs131-137 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs105-119

Array Operations

The provider translates common collection methods to PostgreSQL JSON array functions:

• Length or Count maps to jsonb_array_length() or json_array_length().
• Any() (without a predicate) maps to a check that jsonb_array_length > 0.

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs37-42 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs132-139

JSON Functions via EF.Functions

PostgreSQL-specific JSON operators are exposed through EF.Functions. These are particularly useful for jsonb columns to leverage GIN indexes.

Supported Functions

• Containment: EF.Functions.JsonContains(e.Data, "{'key': 'val'}") → data @> '{"key": "val"}'
• Existence: EF.Functions.JsonExists(e.Data, "key") → data ? 'key'
• Key Existence: JsonExistAny (?|) and JsonExistAll (?&) for checking multiple keys.

Sources: test/EFCore.PG.FunctionalTests/Query/JsonStringQueryTest.cs102-113 test/EFCore.PG.FunctionalTests/Query/JsonStringQueryTest.cs197-207 test/EFCore.PG.FunctionalTests/Query/JsonStringQueryTest.cs214-224

Technical Implementation Details

Type Mapping Infrastructure

The NpgsqlTypeMappingSource identifies JSON types based on CLR types or explicit store type names.

• NpgsqlJsonTypeMapping: The primary mapping for JsonDocument, JsonElement, and string mapped to JSON.
• NpgsqlStructuralJsonTypeMapping: Specifically used for EF Core's owned entity JSON support.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlTypeMappingSource.cs89-99 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlJsonTypeMapping.cs1-20

Translation Pipeline

1. Member Access: NpgsqlJsonPocoTranslator or NpgsqlJsonDomTranslator intercepts property access.
2. Expression Creation: A PgJsonTraversalExpression is created (or appended to) to represent the path.
3. SQL Generation: NpgsqlQuerySqlGenerator visits the traversal expression and outputs the appropriate ->, ->>, #>, or #>> operators based on whether the result is used as a JSON object or text.

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs79-101 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonDomTranslator.cs98-110

Array and Collection Mappings

When mapping .NET arrays or lists to JSON, the provider uses NpgsqlArrayTypeMapping. It supports element-level value converters via NpgsqlArrayConverter.

Sources: src/EFCore.PG/Storage/Internal/Mapping/NpgsqlArrayTypeMapping.cs59-68 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlArrayTypeMapping.cs109-123