Trying to figure out what we can do, what we should do, and how this can be scoped and explained.

• JSON path syntax is supported by all 3 built-in dialects. So having a type-safe JSON path builder is a no-brainer.
• scope->JSON path is supported by MySQL & SQLite, PostgreSQL only supports simple references or indices there.
• scope->something->something is only supported by PostgreSQL.
• ->> is supported by all 3 built-in dialects. Since -> is sometimes "JSON represantation" and not real values, we should only support ->> for now, since it aligns all dialects' outputs.
• json_extract(scope, JSON path) is supported by MySQL & SQLite with slight differences in output type, PostgreSQL supports something similiar-ish with jsonb_path_query(scope, JSON path).

Done implementing:

• Easy access to JSON traversal via existing eb.ref - simply add ->/->>/->$/->>$ operator as 2nd argument and chain away with .key(key) & .at(index) methods.

.select((eb) => eb.ref('nicknames', '->').at(5).as('sixth_nickname')) // PostgreSQL (POstgreSQL-style syntax)
.select((eb) => eb.ref('nicknames', '->$').at(5).as('sixth_nickname')) // MySQL (JSON Path syntax)
.select((eb) => eb.ref('nicknames', '->>').at(5).as('sixth_nickname')) // SQLite option 1 (PostgreSQL-style syntax)
.select((eb) => eb.ref('nicknames', '->>$').at(5).as('sixth_nickname')) // SQLite option 2 (JSON Path syntax)
// and of course, can be used in other clauses..
.where((eb) => eb.cmpr(eb.ref('profile', '->').key('created_at'), '>', now))

• Allow creating PostgreSQL (& SQLite) arrow chains (e.g. column->'key'->0) with ->/->>, but also JSON paths (e.g. column->'$.key[location]') (MySQL & SQLite) ->$/->>$ in a dialect agnostic way (meaning no specific compiler code). For nested values to return as their true types at runtime and not be stringified, use single > arrows (->/->$) for PostgreSQL/MySQL and double > arrows (->>/->>$) for SQLite.

• in Database interface, define your json columns as objects/arrays or ColumnType helper type with first slot being objects/arrays or the new JSONColumnType helper type (short for ColumnType<S extends object | null, I = string, U = string>).

interface Person {
  nicknames: string[],
  profile: {
    created_at: string;
  },
  other_nicknames: ColumnType<string[], string, string>,
  profile: JSONColumnType<{ created_at: string }> // same as `ColumnType<{ created_at: string }, string, string>`
}

• Need to make sure users of prisma-kysely & kysely-codegen can override whatever is generated for arrays and json columns with manual interfaces. We also support optional keys (key?: type), the inferred result type would be nullable.

• JSONPathBuilder can allow creating standalone JSON paths in the future, e.g. as arguments of sql json functions. maybe even expose it in ExpressionBuilder?

• SQLite requires JSON parsing of results, so this PR includes a very simple ParseJSONResultsPlugin.

new Kysely({
  dialect: sqliteDialect,
  plugins: [new ParseJSONResultsPlugin()],
})

• Should we offer a non-type-safe way to do the same from sql.ref? Might be a low hanging fruit by simply using JSONPathBuilder<any> (haven't checked yet).

• Need to write a proper recipe for all of this, make it dialect-specific like "Getting Started".