Table of Contents

JSON support across providers

There is no single portable JSON API: PostgreSQL exposes a native json/jsonb surface, SQL Server returns a document with a trailing FOR JSON clause and offers JSON-as-text functions, MySQL/MariaDB offer the same JSON-as-text functions over the JSON_EXTRACT/JSON_SET family, and the remaining providers reject JSON constructs.

Prerequisites: Querying and projections · Scalar functions · Table-valued functions · Provider overview

Overview

nextorm deliberately has no cross-provider JSON function surface. "Working with JSON" means different things on different providers, and the engine keeps those mechanisms separate instead of pretending they are one feature:

  • SQL Server has two independent surfaces. ForJson executes the query and returns the whole result set as one JSON document (FOR JSON PATH/FOR JSON AUTO), and SqlServer provides the JSON-as-text scalar functions (json_value, json_query, json_modify, isjson) plus the openjson table function. SQL Server 2025 (17.x) additionally has a distinct native json column type, reachable for [JsonColumn] only through an explicit version gate — see Native json on SQL Server 2025+.
  • PostgreSQL has native json/jsonb types and exposes the only json/jsonb function/operator surface (SqlFunctions.Postgres): construction, aggregation, access, containment and JSONPath.
  • MySQL and MariaDB expose the same JSON-as-text surface as SQL Server (SupportsTextJson) over the JSON_EXTRACT/JSON_UNQUOTE/JSON_SET family. Their JSON columns are not the same thing: MySQL 8 has a real json type (the server validates, normalises and reports it as json), while MariaDB's JSON is an alias for LONGTEXT plus a json_valid() CHECK (reported as longtext). Both are used through the text transport — a [JsonColumn] property serializes to text that the server parses — because neither provider has a bindable native JSON representation, so JsonColumnStorage.Native is refused there (see Map a CLR object to a native JSON column).
  • ClickHouse maps its string-JSON extractors (JSONExtractString, JSONExtractInt, JSONExtractFloat, JSONExtractBool, JSONExtractRaw, JSONHas, JSONLength, JSONType) — plus the array-returning JSONExtractKeys/JSONExtractArrayRaw (projecting as string[]) and JSONExtractKeysAndValues (projecting as Tuple<string, T>[]) — and the flat-JSON fast path (visitParamExtractString/Int/Float/Bool/Raw) through SupportsJsonExtract, its JSONPath scalars JSON_VALUE/JSON_QUERY/JSON_EXISTS (written as json_value/json_query/json_exists) and its native-JSON functions JSONAllPaths/JSONAllPathsWithTypes/toJSONString (written as json_all_paths/json_all_paths_with_types/to_json_string) through the same flag. These take a native JSON value (CAST(col AS JSON) for a String column); JSONAllPaths projects as string[] and JSONAllPathsWithTypes as Map(String, String) → Dictionary<string, string>. ClickHouse also has a native JSON column type (object-rooted): a [JsonColumn] property and a bare JsonObject/JsonDocument/JsonElement projection/parameter map to it (see Map a CLR object to a native JSON column).
  • SQLite exposes the JSON1 surface through the provider-specific SqlFunctions.Sqlite (json/jsonb/ json_extract, the ->/->> operators, json_set/json_patch, and the json_each/json_tree table functions). The cross-provider JSON API is not mapped to it: the native json/jsonb type and the text-JSON names (json_value/json_query/json_modify/isjson) throw NotSupportedException, so use the JSON1 members directly (see the SQLite provider).
  • Every SQL provider shares one JSON terminal: WriteJson / WriteJsonAsync stream the query's projection to a caller-owned Stream as a JSON array or NDJSON, serialized client-side with System.Text.Json (see Streaming data to a Stream). On SQL Server an eligible flat projection is instead served by the database through a trailing FOR JSON PATH, whose single document column is copied to the stream in bounded chunks — an internal optimization with the same output contract, not a change to the scalar ForJson below (see SQL Server native fast-path). Preflight validation uses a single exception contract — NotSupportedException with the message convention JSON streaming validation [<token>]: <context>. and the fixed tokens mode-options, projection, names, unsupported-column, reader-binding, in-memory and unsupported-execution-form — and an incompatible reader schema is rejected before any output, framing included. This is a serialization terminal, not a SQL JSON function: it does not change the per-provider JSON surfaces below.

Because the mechanisms are different, the same conceptual result is written in different ways. Read the section for your provider; the provider matrix and choosing an approach show the side-by-side equivalents.

Provider matrix

Provider ForJson output Native json/jsonb JSON-as-text functions openjson as FROM
SQL Server Supported (SupportsForJson) json on 2025+ (SupportsJson), opt-in through a declared major version 17+ or an Azure/Fabric SQL database engine edition; object/array roots only. Otherwise JSON lives in nvarchar Supported (SupportsTextJson) Supported
PostgreSQL No Supported (SupportsJson) No No
SQLite No No No (JSON1 via SqlFunctions.Sqlite) No
MySQL / MariaDB No Real json column (MySQL) / JSON = LONGTEXT + json_valid() CHECK alias (MariaDB), both used through text storage; Native refused Supported (SupportsTextJson) No
ClickHouse No Native JSON (SupportsJson; object-rooted, via [JsonColumn]/JsonObject/JsonDocument/JsonElement) String JSON + JSONPath scalars + native-JSON functions (JSONAllPaths/JSONAllPathsWithTypes/toJSONString; SupportsJsonExtract) No
In-memory Not applicable (no SQL) Not applicable Not applicable Not applicable

"No" means the command is rejected with NotSupportedException when its SQL is built, not that the database lacks JSON support.

SQL Server

Return the whole result set as one JSON document

ForJson is a terminal operator: it executes the query and returns the whole result set as one JSON document (the projection drives the document shape; the query element type is irrelevant because the database returns a single document column):

string? json = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Id, e.String })
    .ForJson(ForJsonMode.Path, root: "items", includeNullValues: true);

select id, somestring
 from complex_entity for json path, root('items'), include_null_values

Path shapes the document from the projection aliases (the default) and Auto from the table structure:

string? json = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Id, e.String })
    .ForJson(ForJsonMode.Auto);

select id, somestring
 from complex_entity for json auto

The optional root wraps the document in ROOT('name') and includeNullValues adds INCLUDE_NULL_VALUES. ForJson returns null when the query produces no rows (SQL Server returns SQL NULL for an empty FOR JSON result). The same server-side FOR JSON PATH rendering also backs the WriteJson native fast-path for an eligible flat projection; that path is an internal optimization and does not change ForJson itself (see SQL Server native fast-path). The clause is placed after ORDER BY and before a trailing OPTION (...), so it composes with Hint (apply the hint first, then the terminal): a for json path option (recompile) query is valid. Use WithForJson to only attach the clause and keep the command composable. (Table hints, WithTableHint, attach to the FROM table and are independent of the JSON clause.) A dialect that does not support the clause rejects the command, and combining ForJson with ForXml throws NotSupportedException("FOR JSON and FOR XML cannot be combined.").

Read JSON stored in a text column

SQL Server before 2025 and MySQL/MariaDB expose no bindable native JSON representation here: JSON is stored in an ordinary text column and manipulated with text-oriented functions (SupportsTextJson). The path is a JSONPath string ('$.name'); json_value returns a scalar, json_query an object/array fragment and json_modify a modified copy. On MySQL/MariaDB the same expressions render as json_unquote(json_extract(...)), json_extract(...) and json_set(...). On SQL Server 2025 the same functions keep working over both an nvarchar column and the native json type (see Native json on SQL Server 2025+):

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        Id = SqlFunctions.SqlServer.json_value(e.String, "$.id"),
        Name = SqlFunctions.SqlServer.json_query(e.String, "$.name"),
        Updated = SqlFunctions.SqlServer.json_modify(e.String, "$.id", "1")
    })
    .ToList();

select json_value(somestring, '$.id') as [Id], json_query(somestring, '$.name') as [Name], json_modify(somestring, '$.id', '1') as [Updated]
 from complex_entity

SqlFunctions.SqlServer.isjson tests whether a text value is valid JSON. On SQL Server a predicate renders (isjson(x)) = 1 because T-SQL ISJSON returns an int, and as a projected value it is cast to bit; on MySQL/MariaDB it renders json_valid(x):

var rows = dataContext.From<IComplexEntity>()
    .Where(e => SqlFunctions.SqlServer.isjson(e.String))
    .Select(e => new { e.Id })
    .ToList();

select id
 from complex_entity where (isjson(somestring)) = 1

Native json on SQL Server 2025+

SQL Server 2025 (17.x) has a distinct built-in json type — not an alias of nvarchar — and the Azure SQL engines built on the same release have it too. nextorm reaches it through the same [JsonColumn] mapping, gated on a declared state: a context built with Version(17, 0) or higher, or with an explicit SqlServerEngineEdition of SqlDatabase (5), AzureSqlManagedInstance (8) or MicrosoftFabricSqlDatabase (12). The default, unversioned construction stays text-only, so an existing nvarchar-backed mapping never changes under you:

// Azure SQL Database / Fabric SQL database: declare the engine edition, not a fake major version.
using var azure = new SqlServerDataContext(SqlServerEngineEdition.SqlDatabase, connectionString);

// Version-first constructor: the declared major version opens the version-gated capabilities.
using var dataContext = new SqlServerDataContext(new Version(17, 0), connectionString);

[JsonColumn(Storage = JsonColumnStorage.Native)]   // Auto and Text are equally valid here
public Address Address { get; set; } = new();

The representation stays the ordinary CLR string: parameters are inferred as nvarchar, there is no DOM parameter, no SqlDbType.Json and no double serialisation. On a gate-open context Auto, Text and Native resolve to the same text converter, so the stored bytes are identical to the text storage — the column type, not the converter, is what differs. What the native column adds is a server-side constraint: it stores object and array documents only, and rejects the scalar roots (string, number, boolean) and the JSON null document with server error 13609 — use the text storage for those payloads. That rejection is the server's, raised at execution; nextorm adds no client-side rule of its own.

Edition 11 ("Azure Synapse serverless SQL pool, or Microsoft Fabric") does not grant the capability by edition; only 5, 8 and 12 do. nextorm never probes SERVERPROPERTY('EngineEdition') — the edition is a declaration, immutable per context type, and SQL generation depends only on the declared state. Azure SQL Database, Managed Instance and Fabric SQL database are unit-tested only: no live Azure/Fabric instance was available for this validation, and the claim does not extend to every Fabric workload. nextorm also generates no column DDL, so switching an existing column to json stays a deployment/migration decision.

Turn JSON into rows with openjson

SqlFunctions.SqlServer.openjson(json) is a table-valued function (SQL Server 2016+) used through From with a factory call. Its default schema yields the properties of a JSON object or the elements of a JSON array as SqlFunctions.IOpenJsonRow (Key/Value/Type):

var json = """{"a":1,"b":2}""";

var entries = dataContext
    .From(() => SqlFunctions.SqlServer.openjson(json))
    .Select(r => new { r.Key, r.Value, r.Type })
    .ToList();

select [key] as [Key], value, type
 from openjson(@json) as [t1]

For a typed projection declare your own TvfEntityBuilder<T> factory whose row shape matches the WITH (...) clause and pass the clause body as withClause; nextorm only emits the call, it does not create the function. SqlFunctions.SqlServer.string_split follows the same pattern for a comma-separated string. Both are gated by SupportsTableFunction, so only SQL Server emits them.

PostgreSQL

Pass a JSON value as a parameter

A parameter whose runtime value is a JsonDocument, JsonElement or JsonNode is bound as jsonb, so it can be used directly with the JSON operators:

using System.Text.Json;

var document = JsonDocument.Parse("""{"name":"Alice","tags":["a","b"]}""");

var rows = dataContext.From<IComplexEntity>()
    .Where(e => SqlFunctions.Postgres.json_get_text(SqlFunctions.Parameter<JsonDocument>(0), "name") == "Alice")
    .Select(e => new { e.Id })
    .ToList(document);

select id
 from complex_entity where ((@norm_p0 ->> 'name') = 'Alice')

Provider parameter binding: a plain JSON string parameter is bound as text. The six accessors json_get, json_get_text, json_get_path and json_get_path_text (both the string-key and the integer-index overloads) are the exception — they apply contextual SQL typing: a raw client document value (a literal, a captured value or an explicit parameter) is parsed as jsonb in the generated SQL, and the right operand is typed per overload (text for a string key, integer for an index, text[] for a path). Native json/jsonb columns and server-side JSON expressions are not cast and keep their operand and result types. For every other JSON function, parse a plain string explicitly with SqlFunctions.Postgres.json_cast(value) (cast(value as jsonb)).

Map a CLR object to a native JSON column

A [JsonColumn] property stores any serializable CLR object in the column as JSON. The default JsonColumnStorage.Auto uses the provider's native type where it has one, so on PostgreSQL the property maps to a native jsonb column for both read and write — insert, update and RETURNING — while Native forces jsonb and Text forces a text column. The property participates in projections and constant comparisons like any other converted column:

[SqlTable("customers")]
public sealed class Customer
{
    public long Id { get; set; }

    [JsonColumn]
    public Address Address { get; set; } = new();
}

The mapping covers the property, not the schema: nextorm does not generate the column DDL, so the table must already have the jsonb column (the same holds for SQL Server's native json column — see Native json on SQL Server 2025+). See Value converters for the fluent JsonColumn(o => ...) form and serializer options.

On MySQL and MariaDB the same attribute keeps the text storage, because those providers have a JSON column but no bindable native JSON representation (MySqlConnector rejects JsonElement, JsonDocument and JsonNode parameters, MySqlDbType.JSON accepts strings only, and a json column always reads back as string). This is fully supported storage, not a refusal of JSON columns: a MySQL 8 table may declare the column as json — the server validates and normalises the document — and a MariaDB table may declare it as JSON, which the server reports as longtext with a json_valid() CHECK. An explicit JsonColumnStorage.Native on either engine fails closed with a NotSupportedException naming the dialect (MySqlDialect/MariaDbDialect) before any command runs; use Auto (text) or Text. Note also that MySQL compares a json document as a typed value — a string operand does not equal it — and nextorm promises no equality translation for that column.

Bare CLR JSON values are handled at the boundary, differently from a mapped column:

  • A JsonDocument or JsonElement value materializes from a json/jsonb column in a named projection, and a parameter of either type binds as jsonb.
  • A bare JsonNode/JsonNode? property (declared exactly JsonNode, not JsonObject/JsonArray) reads a native json/jsonb column: the text is read and parsed, covering object, array and scalar roots, in both scalar (Select(x => x.Data)) and composite projections. A SQL NULL and a JSON literal null both become CLR null. Declared JsonObject/JsonArray properties, and a bare JsonDocument/JsonElement scalar projection, are outside this support — map those with [JsonColumn] or project them in a named shape. A JsonNode parameter binds as jsonb.
  • A plain string maps to text. The six accessors above (json_get, json_get_text, json_get_path, json_get_path_text, string-key and integer-index overloads) accept a raw client document string — a literal, a captured value or an explicit parameter — because they apply contextual SQL typing and parse it as jsonb in the generated SQL, typing the key/index/path as text/integer/text[]. A native json/jsonb column or a server-side JSON expression is left uncast, so its operand and result types do not change. For any other JSON function, use SqlFunctions.Postgres.json_cast(value) to parse a string.

On ClickHouse the same section header applies to the native JSON column, whose physical type is JSON and which is object-rooted: it accepts JSON objects at the root and rejects arrays and scalar roots. A [JsonColumn] property with Auto or Native storage maps an object-root POCO, JsonObject, JsonDocument or JsonElement to the native JSON type for both read and write, while Text keeps a String column; a model that does not serialize to an object fails closed with NotSupportedException. The same object-root DOM values map as bare projections and parameters. The projection must go through a named shape — Select(x => new { x.Doc }) or a DTO — because a bare top-level scalar Select(x => x.Doc) is not handled by the core projection classifier. An empty object {} stays a non-null value, while SQL NULL in a Nullable(JSON) column stays distinct from it (null for JsonObject/JsonDocument, default/JsonValueKind.Undefined for the value-type JsonElement). A bare string read over a JSON column is not mapped. The legacy Object('json') alias is not supported by this integration: with ClickHouse.Driver 1.4.0.0 and ClickHouse 25.8.33.6 a connection-level allow_experimental_object_type=1 lets the type be created, but reads materialize as Tuple<SByte,String> (path → value) and cannot be read through the native JsonObject transport (GetFieldValue<JsonObject> throws InvalidCastException). nextorm does not enable allow_experimental_object_type itself.

Build a JSON object or array in the projection

Construction functions build a json/jsonb value from ordinary SQL expressions. String arguments that are constants become the keys:

var document = dataContext.From<IComplexEntity>()
    .Select(e => new { V = SqlFunctions.Postgres.jsonb_build_object("id", e.Id, "name", e.String) })
    .First()
    .V;

select jsonb_build_object('id', id, 'name', somestring) as "V"
 from complex_entity
C# SQL
SqlFunctions.Postgres.jsonb_build_object("a", x, ...) jsonb_build_object('a', x, ...)
SqlFunctions.Postgres.jsonb_build_array(x, y) jsonb_build_array(x, y)
SqlFunctions.Postgres.json_array(x, y) / jsonb_array(x, y) json_array(x, y) / json_array(x, y returning jsonb)
SqlFunctions.Postgres.to_jsonb(x) to_jsonb(x)

Collapse a result set into one document (the FOR JSON analogue)

PostgreSQL has no FOR JSON suffix; the equivalent is the jsonb_agg aggregate over a projection. Use jsonb_agg/json_agg for an array of values or jsonb_object_agg for a key/value map:

var json = dataContext.From<IComplexEntity>()
    .Select(e => SqlFunctions.Postgres.jsonb_agg(
        SqlFunctions.Postgres.jsonb_build_object("id", e.Id, "name", e.String)))
    .First();

select jsonb_agg(jsonb_build_object('id', id, 'name', somestring))
 from complex_entity

Nesting is expressed with GroupBy: the outer aggregate makes the document, the inner one makes each group's array:

var groups = dataContext.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new
    {
        e.Int,
        Items = SqlFunctions.Postgres.jsonb_agg(
            SqlFunctions.Postgres.jsonb_build_object("id", e.Id, "name", e.String))
    })
    .ToList();

-- jsonb_agg(jsonb_build_object('id', id, 'name', somestring)) grouped by int

json_object_agg(key, value) / jsonb_object_agg(key, value) produce a JSON object instead of an array (jsonb_object_agg(id, somestring)).

Read and filter JSON

The access operators and predicates are exposed as SqlFunctions.Postgres methods:

C# SQL
SqlFunctions.Postgres.json_get(json, "key") / json_get(json, 0) json -> key / json -> 0
SqlFunctions.Postgres.json_get_text(json, "key") / json_get_text(json, 0) json ->> key / json ->> 0
SqlFunctions.Postgres.json_get_path(json, path) / json_get_path_text(json, path) json #> path / json #>> path
SqlFunctions.Postgres.json_contains(a, b) a @> b
SqlFunctions.Postgres.json_exists(json, "key") json ? 'key'
SqlFunctions.Postgres.json_exists_any(json, keys) / json_exists_all(json, keys) json ?\| keys / json ?& keys
SqlFunctions.Postgres.jsonb_path_exists(json, path) / jsonb_path_match(json, path) jsonb_path_exists(json, cast(path as jsonpath)) / jsonb_path_match(...)
SqlFunctions.Postgres.jsonb_path_query_first(json, path) / jsonb_path_query_array(json, path) jsonb_path_query_first(...) / jsonb_path_query_array(...)

The four accessors above apply contextual SQL typing: a raw client document value (a literal, a captured value or an explicit parameter) is parsed as jsonb, so json_get("{\"a\":1}", "a") renders cast('{"a":1}' as jsonb) -> cast('a' as text), while a native json/jsonb column or a server-side JSON expression is emitted unchanged (doc -> cast('a' as text)), preserving its operand and result types. The key/index/path is always typed for its overload: cast(<key> as text), cast(<index> as integer), cast(<path> as text[]) — a literal key is inlined as cast('a' as text). An ordinary string parameter is bound as text.

A path/keys operand is a string[] bound as a single array parameter, so json_get_path(json, new[] { "a", "b" }) renders json #> cast(@p0 as text[]). JSONPath functions take the path as a plain string and cast it to jsonpath; JSON_EXISTS, jsonpath and the sibling JSON functions keep their own independent behavior.

var path = new[] { "a", "b" };

var rows = dataContext.From<IComplexEntity>()
    .Where(e => SqlFunctions.Postgres.json_contains(
        SqlFunctions.Parameter<JsonDocument>(0),
        SqlFunctions.Parameter<JsonDocument>(1)))
    .Where(e => SqlFunctions.Postgres.json_exists(SqlFunctions.Parameter<JsonDocument>(2), "key"))
    .Select(e => new { e.Id })
    .ToList();

select id
 from complex_entity where (@norm_p0 @> @norm_p1) and (@norm_p2 ? 'key')

SQL/JSON query functions

The SQL/JSON query functions take a jsonpath (cast from the path string) and select a value rather than a key:

C# SQL
SqlFunctions.Postgres.json_value(json, path) json_value(json, cast(path as jsonpath)) (returns text)
SqlFunctions.Postgres.json_query(json, path) json_query(json, cast(path as jsonpath)) (returns jsonb)
SqlFunctions.Postgres.json_exists(json, path, fromJsonPath) json_exists(json, cast(path as jsonpath)) (returns boolean) 
var json = SqlFunctions.Postgres.jsonb_build_object("a", 1);

var rows = dataContext.From<IComplexEntity>()
    .Select(e => new
    {
        Value = SqlFunctions.Postgres.json_value(json, "$.a"),
        Exists = SqlFunctions.Postgres.json_exists(json, "$.a", true)
    })
    .ToList();

select json_value(jsonb_build_object('a', 1), cast('$.a' as jsonpath)) as "Value",
       json_exists(jsonb_build_object('a', 1), cast('$.a' as jsonpath)) as "Exists"
 from complex_entity

json_exists has two forms: the two-argument json_exists(json, "key") is the top-level ? operator, while the three-argument json_exists(json, path, fromJsonPath) is the SQL/JSON path function (the third argument is a discriminator and is not rendered into SQL).

The construction and aggregation functions return string?; deserialize with JsonSerializer.Deserialize<T>(...) when you need a .NET object.

Choosing an approach

Goal SQL Server PostgreSQL
One JSON document for the whole result set .ForJson(...) jsonb_agg(jsonb_build_object(...))
One JSON object per row json_query/text concatenation, or assemble on the client jsonb_build_object(...)
Read a field from a JSON value json_value(col, '$.x') col ->> 'x'
Test that a value is JSON isjson(col) the value is typed json/jsonb by the column
Filter by JSON content json_value(col, '$.x') = ... col @> ..., col ? 'x', col #> ...
Expand JSON into rows openjson(...) as a FROM source no built-in helper; declare a TvfEntityBuilder<T> factory

Limitations

  • There is no portable JSON abstraction. Code written for one provider's JSON surface throws NotSupportedException on another; use ISqlDialect capability flags if you must branch.
  • The JSON streaming terminal validates its options and projection up front and fails with NotSupportedException under the stable JSON streaming validation [<token>]: <context>. convention (tokens mode-options, projection, names, unsupported-column, reader-binding, in-memory, unsupported-execution-form); an incompatible reader schema is rejected before any output, the array/root framing included. Lifecycle failures stay InvalidOperationException/ObjectDisposedException, while destination/provider errors and cancellation keep their category. See Validation exceptions and runtime errors.
  • ForJson/ForXml are SQL Server only (SupportsForJson / SupportsForXml), and the two are mutually exclusive on one command.
  • The PostgreSQL json/jsonb surface (SqlFunctions.Postgres) requires SupportsPostgresJsonSql (PostgreSQL only); the SQL Server and MySQL/MariaDB text functions require SupportsTextJson. Each throws on the other provider.
  • ClickHouse's JSON surface (JSONExtract*/visitParamExtract*, the JSONPath scalars and the native-JSON JSONAllPaths/JSONAllPathsWithTypes/toJSONString) requires SupportsJsonExtract; it does not overlap with the SupportsTextJson set. The native-JSON functions take a native JSON value (cast a String column with CAST(col AS JSON)); json_all_paths projects as string[] and json_all_paths_with_types as Dictionary<string, string> (mapKeys/mapValues turn the map into a collection). A native JSON column stores native JSON for [JsonColumn] (Auto/Native, object-root POCO/JsonObject/ JsonDocument/JsonElement) and for bare object-root JsonObject/JsonDocument/JsonElement projections/parameters (see Map a CLR object to a native JSON column). Arrays/scalar roots, a bare string, and the legacy Object('json') alias are not supported, and a bare top-level scalar Select(x => x.Doc) is not handled by the core projection classifier.
  • SQLite's JSON1 extension is exposed only through the provider-specific SqlFunctions.Sqlite surface (json_extract, ->/->>, json_each/json_tree, ...); the cross-provider native/json_* API is not mapped to it, so those names throw NotSupportedException.
  • The in-memory provider produces no SQL, so ForJson/ForXml throw NotSupportedException and the JSON function surfaces do not apply to it.
  • PostgreSQL maps a [JsonColumn] property to a native jsonb column (Auto storage) on read and write (insert, update and RETURNING), and a JsonDocument/JsonElement/JsonNode parameter binds as jsonb. ClickHouse maps [JsonColumn] Auto/Native and bare object-root JsonObject/JsonDocument/JsonElement to its native object-rooted JSON column. SQL Server maps [JsonColumn] Auto/Native to its native json type only on a context that declares major version 17 or higher, or an Azure/Fabric SQL database engine edition, and then only for object/array documents — a scalar root or the JSON null document is refused by the server with error 13609, so those payloads need Text storage; on a pre-2025 context with no version-17-equivalent edition declaration SQL Server JSON is always text. MySQL/MariaDB JSON columns are reached through the text transport on both engines (Auto/Text), and Native is refused with the dialect-named NotSupportedException.

See also

Source: src/nextorm.core/Visitors/Structured/JsonSqlTranslator.cs, src/nextorm.core/Query/SqlFunctions/SqlFunctions.Postgres.cs, src/nextorm.core/Query/SqlFunctions/SqlFunctions.SqlServer.cs, src/nextorm.core/Expressions/Formats/ForJson.cs, src/nextorm.core/DataContext/Dialect/ISqlDialect.cs, src/nextorm.sqlserver/SqlServerDialect.cs, src/nextorm.postgres/PostgresDialect.cs; tests tests/nextorm.sqlserver.tests/SqlGeneration/SqlGenerationTests.cs:443,695,1313,1390, tests/nextorm.postgres.tests/SqlGeneration/SqlGenerationTests.cs:848,868,887,930,950,1831,1861, tests/nextorm.sqlite.tests/SqlGeneration/SqlGenerationTests.cs:1773.

Edit this page