Skip to main content

JSON Traversal

In the era of data-driven applications, SQL databases such as PostgreSQL, MySQL, and SQLite have evolved to support JSON traversal. JSON provides a flexible and dynamic data structure, and these databases now offer native support for storing, querying, and manipulating JSON data within the SQL framework.

PostgreSQL, MySQL, and SQLite have introduced native JSON data types, along with functions and operators for extracting values, filtering conditions, aggregating arrays, and indexing JSON data. This enables developers to leverage the strengths of SQL and JSON together, combining relational querying with the flexibility of JSON manipulation.

These capabilities have always fascinated us, and finally we are able to bring them to Kysely and through some exploration and experimentation, we'll hopefully land together on a solution that is both elegant, practical, and most importantly, type-safe.

Each dialect Kysely supports at its core, has a different way of handling JSON traversal. So this recipe will require choosing a dialect to work with:

Defining Types​

When defining a JSON column's type, it must follow the following rules:

Root column type - The root select type must be of object or array type. It can be nullable, but cannot be optional (jsonColumn?:), just like any other column type definition. Its insert and update types must be strings, as you'd JSON.stringify JSON parameters.

Nested field type - Nested fields must have a JSON native type (string, number, boolean, null, object or array). Unlike the root column, nested fields can be optional (field?:).

Unknowns, JSON (Discriminated) Unions and other complexities - Supporting traversal to not-well-defined JSONs or complex types was not part of phase 1. It could work right now, but we haven't tested it. We'd appreciate any feedback or real-world examples, as we prepare for the next phases.

src/types.ts
import { ColumnType, Generated, JSONColumnType } from 'kysely'
        
export interface Database {
  person_metadata: PersonMetadataTable
}

export interface PersonMetadataTable {
  profile: JSONColumnType<{ created_at: string }> // short for ColumnType<T, string, string>
}

Querying​

Inference​