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(new JsonStringEnumConverter()));

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 (Query query, IDapperQueryService svc) =>
{
    query.Validate(rules =>
    {
        rules.Select(c => c.Deny("PasswordHash"));
        rules.Where(c  => c.Deny("PasswordHash"));
        rules.PageSize(p => p.Max(100));
    }, QueryValidationMode.SilentStrip);

    var dq = DapperQueryBuilder.FromBase(query).ForObject("Users", "dbo").Build();

    return await svc.QueryAsync<User>(dq);
})
.Accepts<Query>("application/json")
.Produces<QueryResult<User>>();

MVC controller

[HttpPost("query")]
public async Task<ActionResult<QueryResult<User>>> Query([FromBody] Query query)
{
    query.Validate(rules => rules.PageSize(p => p.Max(100)), QueryValidationMode.SilentStrip);

    return Ok(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);
    return Results.Ok(await svc.QueryAsync<User>(dq));
}
catch (QueryValidationException ex)
{
    return Results.ValidationProblem(
        ex.InvalidProperties.ToDictionary(p => p, p => new[] { "Denied by security policy" }));
}

---

A TypeScript client type

export type Logic = 0 | 1 | 2 | 3;          // And, Or, AndNot, OrNot
export type SortOrder = 0 | 1;              // Ascending, Descending
export type ConditionOperator =
  | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10;

export interface Condition {
  columnName: string;
  operator: ConditionOperator;
  value?: unknown;
  valueTo?: unknown;
}

export interface ConditionGroup { logic?: Logic; conditions?: Condition[]; }
export interface QueryCriteria  { logic?: Logic; groups?: ConditionGroup[]; }
export interface QueryPaging    { size?: number; number?: number; }
export interface ColumnDescriptor { columnName: string; sortOrder?: SortOrder; }

export interface Query {
  criteria?: QueryCriteria;
  paging?: QueryPaging;
  selectColumns?: string[];
  sortColumns?: ColumnDescriptor[];
  groupByColumns?: ColumnDescriptor[];
}

export interface HierarchyNode<T> {
  key: unknown;
  count: number;
  subGroups?: HierarchyNode<T>[] | null;
  items?: T[] | null;
}

export interface QueryResult<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.