No Standard JSON for SQLQueries: Why It Matters

by

Summary

The discussion centers on the lack of a standardized, public JSON representation for SQL queries. While mapping a simple SELECT statement to a structured JSON object appears intuitive and “clean,” the industry has not converged on a single specification. This postmortem analyzes why the abstraction of relational algebra into a serialized format is significantly more complex than a simple key-value mapping.

Root Cause

The perceived simplicity of the example provided is a result of oversimplification of the SQL grammar. A universal JSON spec fails to exist because:

  • Grammatical Ambiguity: SQL is not a single language but a family of dialects (PostgreSQL, MySQL, T-SQL, Oracle) with varying syntax for joins, window functions, and proprietary extensions.
  • Operator Complexity: Mapping a WHERE clause is trivial for eq, but becomes exponentially harder for BETWEEN, LIKE, IN, EXISTS, and complex nested subqueries.
  • Schema Awareness: A true representation must decide whether it represents the syntax (the string structure) or the semantics (the logical execution plan).
  • Non-Deterministic Ordering: JSON objects are unordered by definition, whereas SQL query structure often relies on strict operator precedence and clause ordering.

Why This Happens in Real Systems

In production environments, we rarely need to “re-represent” SQL as JSON because we operate at different layers of the stack:

  • Application Layer: Developers use ORMs (Object-Relational Mappers). The ORM handles the translation from objects to SQL, making a middle-man JSON spec redundant.
  • Driver Layer: Communication between an app and a database happens via binary protocols (e.g., the PostgreSQL wire protocol) which are optimized for performance and type safety, not human-readable serialization.
  • Query Engine Layer: Databases convert SQL into an Abstract Syntax Tree (AST) or a logical plan. These internal representations are highly optimized and specific to the engine’s execution engine, making them unsuitable for a public standard.

Real-World Impact

The lack of a standard forces engineers into several suboptimal patterns:

  • Vendor Lock-in: If a company builds a middleware layer that relies on a custom JSON-to-SQL parser, they are effectively locked into that specific implementation.
  • Increased Latency: Implementing an extra serialization/deserialization step (SQL $\rightarrow$ JSON $\rightarrow$ AST) adds unnecessary CPU overhead and latency to the request lifecycle.
  • Security Risks: Building custom parsers to interpret JSON-based queries increases the attack surface for SQL Injection if the translation logic is not mathematically perfect.

Example or Code

{
  "query": {
    "type": "select",
    "projections": ["*"],
    "source": {
      "table": "metadata",
      "alias": "m"
    },
    "filters": [
      {
        "operator": "AND",
        "conditions": [
          {
            "column": "tags",
            "operator": "CONTAINS",
            "value": "finance"
          },
          {
            "column": "created_at",
            "operator": "GT",
            "value": "2023-01-01T00:00:00Z"
          }
        ]
      }
    ],
    "limit": 100
  }
}

How Senior Engineers Fix It

Senior engineers do not try to reinvent the wheel by creating new query languages. Instead, they use established patterns:

  • Use Existing Standards: Instead of a custom JSON spec, use GraphQL if the goal is to provide a structured, queryable interface to data over HTTP.
  • Leverage AST Libraries: If a custom parser is required, use a formal parser generator (like ANTLR) to build a robust Abstract Syntax Tree rather than manual JSON mapping.
  • Protocol Buffers: For high-performance internal service communication, use gRPC/Protobuf to define strict, typed schemas rather than loosely typed JSON.

Why Juniors Miss It

Junior engineers often fall into the “Simplicity Trap”:

  • They focus on the “Happy Path”: They see a simple SELECT * and assume the complexity scales linearly, failing to account for edge cases like recursive CTEs or spatial queries.
  • They confuse Syntax with Semantics: They assume that because a query looks easy to write, it is easy to model formally.
  • They underestimate the Cost of Abstraction: They view a JSON middle-layer as “cleaner code” without calculating the operational cost of maintaining a custom parser and the performance penalty it imposes.

Leave a Comment