SqlKata.JsonQuery 0.2.0

SqlKata.JsonQuery

SqlKata.JsonQuery turns a constrained JSON SELECT message into a validated SqlKata.Query over one backend-owned table or view. It is intended for AI tools, HTTP APIs, queues, and other boundaries where a caller needs flexible data retrieval without being allowed to send SQL.

The library constructs queries. It does not open connections, execute commands, authorize users, discover database schema, or expose joins.

SqlKata.JsonQuery is an independent community package. It is not affiliated with, maintained by, or endorsed by the SqlKata project.

Why It Exists

SqlKata composes values through non-raw clauses, but a remote caller can still be dangerous if it controls relation names, column names, operators, raw expressions, or joins. This library closes that gap:

• The backend chooses exactly one table or view.
• The backend declares the allowed columns, physical types, and operations.
• The caller sends only the closed JSON query vocabulary.
• Runtime validation rejects everything outside that contract.
• Accepted values remain compiler-controlled. Most become bindings; a provider may emit a safe literal representation, such as a SQL Server boolean cast.

Views are the intended contract for joined or derived data. If a use case needs joins, put the joins behind a view and query that view as one relation.

Causal Chain

flowchart LR
    A["Table or view contract"] -->|"fixes one relation"| B["Trusted SqlKata seed"]
    C["QueryColumns"] -->|"constrains fields and operations"| D["Runtime validation"]
    C -->|"generates"| E["Relation-specific JSON Schema"]
    E -->|"guides"| F["AI or remote caller"]
    F -->|"emits JSON only"| D
    B -->|"preserves backend predicates"| G["WithJsonQuery"]
    D -->|"permits validated request"| G
    G -->|"returns cloned Query"| H["SqlKata compiler"]
    H -->|"produces SQL and compiler data"| I["Consumer-owned execution"]
    J["Read-only database identity"] -->|"authorizes actual access"| I

Each step removes one class of ambiguity. The model-facing schema improves generation, but WithJsonQuery remains authoritative because callers can ignore or misunderstand a schema.

Installation

Until the first package release, reference the project from source:

<ProjectReference Include="path/to/SqlKata.JsonQuery/SqlKata.JsonQuery.csproj" />

After package publication, the intended package command is:

dotnet add package SqlKata.JsonQuery

Execution libraries such as Dapper and database providers are consumer dependencies, not dependencies of SqlKata.JsonQuery.

Quick Start

1. Define the backend column contract

using SqlKata.JsonQuery;

var columns = QueryColumns.CreateBuilder()
    .Text("account_code", QueryCapability.Select)
    .Date("order_date", QueryCapability.Select | QueryCapability.Sort)
    .Text("order_id")
    .Text(
        "product_name",
        QueryCapability.Select | QueryCapability.Filter | QueryCapability.Sort)
    .Text(
        "region",
        QueryCapability.Select | QueryCapability.Filter |
        QueryCapability.Group | QueryCapability.Sort)
    .Number("order_total")
    .Number(
        "quantity",
        QueryCapability.Select | QueryCapability.Filter |
        QueryCapability.Sort | QueryCapability.Aggregate)
    .Bool("is_active", QueryCapability.Select | QueryCapability.Filter)
    .Build();

The one-argument column methods default to QueryCapability.All. Use explicit capabilities for backend-owned fields. In this example, JSON may select but cannot filter account_code or order_date; trusted tool parameters own those predicates.

Configured names are database identifiers and are emitted verbatim. This is not an ORM mapping layer.

2. Generate model-facing guidance

using System.Text.Json;

JsonElement schema = JsonQuerySchema.Create(columns);
string schemaJson = JsonSerializer.Serialize(
    schema,
    new JsonSerializerOptions { WriteIndented = true });

Expose this relation-specific Draft 2020-12 schema to the model or client. It contains only the columns and operations permitted by columns.

3. Accept a JSON query

{
  "select": ["order_id", "product_name", "region", "order_total", "quantity"],
  "filters": [
    { "col": "order_total", "op": "gt", "value": 1000 },
    {
      "or": [
        { "col": "region", "op": "eq", "value": "EMEA" },
        { "col": "quantity", "op": "gte", "value": 100 }
      ]
    }
  ],
  "order_by": [{ "col": "order_total", "dir": "desc" }]
}

The root filters array is implicit AND. Explicit and and or groups may recurse.

4. Apply it to a trusted seed

using SqlKata;
using SqlKata.JsonQuery;

string[] accounts = ["A100", "A200"];
var effectiveDate = new DateTime(2025, 3, 31);

var seed = new Query("reporting.vOrderSummary")
    .WhereIn("account_code", accounts)
    .Where("order_date", effectiveDate);

Query query = seed.WithJsonQuery(json, columns);

WithJsonQuery validates the JSON and seed, clones the seed, and applies the validated clauses to the clone. Caller OR groups remain beneath the backend's top-level AND scope.

5. Compile and execute outside the library

using Dapper;
using Microsoft.Data.SqlClient;
using SqlKata.Compilers;

var compiler = new SqlServerCompiler { UseLegacyPagination = true };
var compiled = compiler.Compile(query);

await using var connection = new SqlConnection(connectionString);
var rows = await connection.QueryAsync(compiled.Sql, compiled.NamedBindings);

SqlKata.JsonQuery has no Dapper or SQL Server dependency. The example shows one possible consumer.

The library is developed and verified against SQL Server. Other SqlKata compilers are expected to work but are not yet covered by tests.

Common Query Shapes

Group and aggregate

{
  "group_by": ["region"],
  "aggregates": [
    { "fn": "sum", "col": "order_total" },
    { "fn": "count", "col": "order_id" }
  ],
  "order_by": [{ "col": "sum_order_total", "dir": "desc" }]
}

Aggregate aliases are deterministic: {function}_{canonicalColumn}. For the configured lowercase names above, the aliases are sum_order_total and count_order_id.

Inclusive date range

When the tool owns the date range, keep it outside JSON:

var seed = new Query("reporting.vOrderSummary")
    .WhereIn("account_code", accounts)
    .WhereBetween("order_date", startDate, endDate);

var query = seed.WithJsonQuery(json, columns);

As-of snapshot

An as-of request is two operations. Resolve the latest available date within trusted scope, then query that exact date:

var resolver = new Query("reporting.vOrderSummary")
    .WhereIn("account_code", accounts)
    .Where("order_date", "<=", requestedAsOf)
    .AsMax("order_date");

// Compile and execute resolver in the consumer to obtain effectiveDate.

var detail = new Query("reporting.vOrderSummary")
    .WhereIn("account_code", accounts)
    .Where("order_date", effectiveDate)
    .WithJsonQuery(json, columns);

Do not use date <= asOf plus a detail limit as a snapshot. That mixes dates and can discard rows from the desired snapshot.

For multiple accounts or other independently scheduled partitions, define whether the tool promises one common snapshot date or the latest date per partition. Per-partition snapping requires separate queries or a view that owns that relational logic.

Dataset-size guard

var countQuery = query.ToDatasetCountQuery();
var countCommand = compiler.Compile(countQuery);
var actualSize = await connection.ExecuteScalarAsync<long>(
    countCommand.Sql,
    countCommand.NamedBindings);

if (actualSize > maxDatasetSize)
{
    throw new InvalidOperationException(
        $"Dataset contains {actualSize} rows; maximum is {maxDatasetSize}.");
}

The maximum, exception type, and execution policy belong to the consumer. The extension only constructs a count over the exact runnable dataset. A query limit, if present, remains part of that dataset.

Decision Guide

Security Boundary

The library provides a constrained construction boundary, not complete database security.

It enforces:

• one backend-owned table or view;
• backend-owned column names, types, and capabilities;
• a strict SELECT-only JSON vocabulary;
• no caller raw SQL, aliases, joins, subqueries, relation names, or expressions;
• non-raw SqlKata composition and compiler-controlled values;
• strict unknown-property, duplicate-property, value-shape, and clause validation;
• safe-by-default structural limits on payload shape.

Structural limits are enforced by default since 0.2.0: WithJsonQuery applies JsonQueryLimits.Default, bounding payload length, filter-group nesting depth, total filter conditions, and in-list size, with an optional ceiling for limit. Violations reject with the teaching code StructuralLimitExceeded, the offending path, and an actual-versus-allowed detail. Pass a custom JsonQueryLimits to tune the bounds — the generated schema surfaces the active values to the model — or JsonQueryLimits.None to restore the previous unlimited behavior for trusted callers.

The returned SqlKata.Query is mutable. After WithJsonQuery returns, do not append caller-controlled raw SQL, identifiers, expressions, or other unvalidated clauses.

Consumers must still provide:

• authentication and authorization;
• trusted mandatory predicates;
• least-privilege database permissions;
• query timeout, cancellation, and execution policy;
• optional dataset-size enforcement;
• transport-specific error and result shaping.

Documentation

• API reference
• AI integration guide

The full architectural decision history is maintained outside this repository.

Build and Test

dotnet build SqlKata.JsonQuery.sln -c Release
dotnet test SqlKata.JsonQuery.sln -c Release

Portable SQL Server smoke tests are opt-in through SQLKATA_JSONQUERY_LIVE_CONNECTION. The Sharadar reference-use-case tests have a separate, explicit SQLKATA_JSONQUERY_SHARADAR_CONNECTION opt-in because they require private views and pinned reference data. Set both variables to the same Sharadar connection to run the complete live suite. Ordinary builds require neither database nor environment variable.