add json traversal support.

[igalklebanov]

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".

[vercel]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
kysely	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Jul 7, 2023 3:07pm

[koskimas]

I like the idea of a JSON path builder, but I'm not the biggest fan of the jxp('column', '->>', callback) syntax. Seems overly complex. Did you consider something like

jsref('column').key('foo').key('bar').at(0)

you wouldn't be able to specify the operator, but could that be given as an optional second argument?

jsref('column', '->').key('foo').key('bar').at(0)

I'm probably missing some future use cases you had in mind for the jxp function...

[igalklebanov]

I like the idea of a JSON path builder, but I'm not the biggest fan of the jxp('column', '->>', callback) syntax. Seems overly complex. Did you consider something like

jsref('column').key('foo').key('bar').at(0)

you wouldn't be able to specify the operator, but could that be given as an optional second argument?

jsref('column', '->').key('foo').key('bar').at(0)

I'm probably missing some future use cases you had in mind for the jxp function...

Yeah I wasn't feeling jxp because of PostgreSQL. It would force "wrapping hell" to achieve something like col ->> key ->> key.

I like your suggestion, will play around with it more.

[igalklebanov]

Should we add tests that this works correctly with the CamelCasePlugin? The nested keys should be mapped depending on the maintainNestedObjectKeys config.

Haven't thought about that one, will try adding these now.

[koskimas]

Should we add tests that this works correctly with the CamelCasePlugin? The nested keys should be mapped depending on the maintainNestedObjectKeys config.

Haven't thought about that one, will try adding these now.

Maybe we should merge this first and implement the camelCase stuff as a separate PR? We could release a new version after we get this merged?

[igalklebanov]

Maybe we should merge this first and implement the camelCase stuff as a separate PR? We could release a new version after we get this merged?

Yeah, let's go! 🚀

[bombillazo]

Documentation in the Kysely docs site for this would be phenomenal!