JSON Contract

JSON Contract

A Query is designed to be posted by a browser, a mobile app, or a data grid. This page is the wire format: every field, every enum value, and the rules a client can rely on.

---

The complete request shape

Every field is optional. An empty body {} is a valid query — it means "everything, first page of 12".

{
"criteria": {
"logic": 0,
"groups": [
{
"logic": 0,
"conditions": [
{ "columnName": "Country", "operator": 0, "value": "Germany" },
{ "columnName": "Score", "operator": 7, "value": 50 },
{ "columnName": "CreatedOn", "operator": 10, "value": "2024-01-01", "valueTo": "2024-12-31" }
]
}
]
},
"paging": { "size": 20, "number": 1 },
"selectColumns": ["UserId", "FirstName", "LastName", "Country", "Score"],
"sortColumns": [
{ "columnName": "Score", "sortOrder": 1 },
{ "columnName": "LastName", "sortOrder": 0 }
],
"groupByColumns": [
{ "columnName": "Country", "sortOrder": 0 }
]
}

There is no object field. A client cannot name the table, view, or entity it runs against — that is set on the server. See Security.

---

Enum values

Enums are serialized by ASP.NET Core as numbers by default. These values are the C# declaration order and are part of the contract — they will not be reordered.

logic — on criteria and on each group

Value	Name	Joins with	Negates the group
0	And	AND	no
1	Or	OR	no
2	AndNot	AND	yes
3	OrNot	OR	yes

Negation applies only inside a group. On criteria the Not part is ignored.

operator — on each condition

Value	Name	Needs value	Needs valueTo
0	Equals	no — null means IS NULL	no
1	NotEquals	no — null means IS NOT NULL	no
2	Contains	yes	no
3	NotContains	yes	no
4	StartsWith	yes	no
5	EndsWith	yes	no
6	LessThan	yes	no
7	GreaterThan	yes	no
8	LessThanOrEqualTo	yes	no
9	GreaterThanOrEqualTo	yes	no
10	Between	yes	yes

sortOrder — on sort and group descriptors

Value	Name
0	Ascending
1	Descending

Accepting names instead of numbers

If you would rather your clients send "Equals" than 0, register the string converter:

builder.Services.ConfigureHttpJsonOptions(o =>o.SerializerOptions.Converters.Add(newJsonStringEnumConverter()));

JsonStringEnumConverter accepts names case-insensitively on input and writes names on output. This changes your API's contract in both directions — pick one form and document it.

---

Defaults when a field is omitted

Omitted	Behaves as
criteria	no filter — everything matches
criteria.logic	0 (And)
criteria.groups	empty — no filter
a group's logic	0 (And)
a group's conditions	empty — the group contributes nothing
condition.value	null — meaningful only for Equals/NotEquals; makes any other operator unusable
condition.valueTo	null — makes Between unusable
paging	{ "size": 12, "number": 1 }
paging.size ≤ 0	12
paging.number ≤ 0	1
selectColumns	all columns
sortColumns	unordered
groupByColumns	flat result

So the minimal useful body is often just:

{ "paging": { "size": 25, "number": 3 } }

---

How values are unwrapped

value and valueTo are typed object? in C#, which means model binding hands them over as System.Text.Json.JsonElement. ConditionSemantics.Unwrap converts each one before any provider sees it:

JSON kind	Becomes
null, undefined	null
string	string
true / false	bool
number, integral and in range	long
number, otherwise	double
object or array	its raw JSON text as a string

DBNull is also normalized to null, so a value read back out of a data reader behaves the same way.

Because a JSON number always arrives as long or double, and a date always arrives as a string, the type a client sends is rarely the column's type. That is expected and handled — see value coercion below.

---

Type coercion

You do not need to match the column's CLR type in JSON. All of these work against an int Score column:

{ "columnName": "Score", "operator": 7, "value": 50 }
{ "columnName": "Score", "operator": 7, "value": "50" }

The Dapper provider discovers the column's real type from the result set and coerces the value before binding it, so PostgreSQL does not reject integer > text and the database can use the column's index. The EF Core provider does the same against the property's CLR type.

Column type	Send
int, long, decimal, double	a JSON number, or a string containing one
bool	true/false, "true"/"false", or 1/0
DateTime, DateTimeOffset, DateOnly, TimeOnly	an ISO-8601 string — "2024-03-01" or "2024-03-01T14:30:00"
Guid	the usual string form
enum	the member name (case-insensitive) or its numeric value

All parsing is invariant culture, so a request behaves identically regardless of server locale. Send ISO dates; "01/03/2024" is ambiguous and will be read as invariant MM/dd/yyyy.

A value that cannot represent the column's type at all — "abc" for an integer — is not an error. The filter simply matches nothing.

---

Complete examples

Search box with a wildcard-safe term

{
"criteria": { "groups": [ { "logic": 1, "conditions": [
{ "columnName": "FirstName", "operator": 2, "value": "50%" },
{ "columnName": "LastName", "operator": 2, "value": "50%" }
] } ] },
"paging": { "size": 20, "number": 1 }
}

% inside the value is escaped, so this finds the literal text 50% rather than everything starting with 50.

Null checks

{
"criteria": { "groups": [ { "conditions": [
{ "columnName": "DeletedAt", "operator": 0, "value": null },
{ "columnName": "Department", "operator": 1, "value": null }
] } ] }
}

Reads as DeletedAt IS NULL AND Department IS NOT NULL.

A date range

{
"criteria": { "groups": [ { "conditions": [
{ "columnName": "CreatedOn", "operator": 10, "value": "2024-01-01", "valueTo": "2024-12-31" }
] } ] },
"sortColumns": [ { "columnName": "CreatedOn", "sortOrder": 1 } ]
}

Inclusive at both ends. Note that "2024-12-31" is midnight, so a row stamped 2024-12-31 09:00 is excluded — use "2025-01-01" with LessThan, or an explicit end-of-day time, if that matters.

Nested logic: (A OR B) AND NOT C

{
"criteria": {
"logic": 0,
"groups": [
{ "logic": 1, "conditions": [
{ "columnName": "Country", "operator": 0, "value": "Germany" },
{ "columnName": "Country", "operator": 0, "value": "Canada" } ] },
{ "logic": 2, "conditions": [
{ "columnName": "Department", "operator": 0, "value": "HR" } ] }
]
}
}

A two-level grouped dashboard

{
"criteria": { "groups": [ { "conditions": [
{ "columnName": "IsActive", "operator": 0, "value": true } ] } ] },
"paging": { "size": 5, "number": 1 },
"selectColumns": ["UserId", "FirstName", "Score"],
"sortColumns": [ { "columnName": "Score", "sortOrder": 1 } ],
"groupByColumns": [
{ "columnName": "Country", "sortOrder": 0 },
{ "columnName": "Department", "sortOrder": 0 }
]
}

Page size 5 means five countries, each carrying every one of its rows. See Grouping and Hierarchies.

---

Server-side wiring

ASP.NET Core Minimal API

app.MapPost("/api/users/query",async(Queryquery,IDapperQueryServicesvc)=>{query.Validate(rules =>{rules.Select(c =>c.Deny("PasswordHash"));rules.Where(c =>c.Deny("PasswordHash"));rules.PageSize(p =>p.Max(100));},QueryValidationMode.SilentStrip);vardq=DapperQueryBuilder.FromBase(query).ForObject("Users","dbo").Build();returnawaitsvc.QueryAsync<User>(dq);}).Accepts<Query>("application/json").Produces<QueryResult<User>>();

MVC controller

[HttpPost("query")]publicasyncTask<ActionResult<QueryResult<User>>>Query([FromBody]Queryquery){query.Validate(rules =>rules.PageSize(p =>p.Max(100)),QueryValidationMode.SilentStrip);returnOk(await_db.Users.AsNoTracking().ToQueryResultAsync<User>(query));}

Returning 400 on a policy violation

try{query.Validate(rules =>rules.Select(c =>c.Deny("PasswordHash")),QueryValidationMode.ThrowException);returnResults.Ok(awaitsvc.QueryAsync<User>(dq));}catch(QueryValidationExceptionex){returnResults.ValidationProblem(ex.InvalidProperties.ToDictionary(p =>p, p =>new[]{"Denied by security policy"}));}

---

A TypeScript client type

exporttypeLogic=0|1|2|3;// And, Or, AndNot, OrNotexporttypeSortOrder=0|1;// Ascending, DescendingexporttypeConditionOperator=|0|1|2|3|4|5|6|7|8|9|10;exportinterfaceCondition{columnName: string;operator: ConditionOperator;value?: unknown;valueTo?: unknown;}exportinterfaceConditionGroup{logic?: Logic;conditions?: Condition[];}exportinterfaceQueryCriteria{logic?: Logic;groups?: ConditionGroup[];}exportinterfaceQueryPaging{size?: number;number?: number;}exportinterfaceColumnDescriptor{columnName: string;sortOrder?: SortOrder;}exportinterfaceQuery{criteria?: QueryCriteria;paging?: QueryPaging;selectColumns?: string[];sortColumns?: ColumnDescriptor[];groupByColumns?: ColumnDescriptor[];}exportinterfaceHierarchyNode<T>{key: unknown;count: number;subGroups?: HierarchyNode<T>[]|null;items?: T[]|null;}exportinterfaceQueryResult<T>{meta: {total: {rows: number;pages: number};type: 0|1};models: T[];groups: HierarchyNode<T>[];}

---

Contract stability

These are guaranteed not to change without a major version:

• Field names and nesting.
• Enum numeric values and their order.
• The rule that every collection is non-null in a response.
• The rule that an omitted or unusable input is ignored rather than rejected.

What is not guaranteed: the exact SQL a provider emits, and the set of columns a target exposes — both are properties of your database, not of the contract.