JSON utilities for selectNoFrom

[jlarmstrongiv]

Discussed in https://discord.com/channels/890118421587578920/1315878442281209907/1315878442281209907

Currently, all the json utilities expect a column. While it is possible to use these utilities via CTEs:

const exampleJson = {
  foo: {
    bar: 42,
  },
};

function toJson<T>(obj: T): RawBuilder<T> {
  return sql`${JSON.stringify(obj)}`;
}

db.with("my_json_table", (db) =>
      db.selectNoFrom((eb) => toJson(exampleJson).as("my_example_json")),
    )
      .selectFrom("my_json_table")
      .select((eb) => eb.ref("my_example_json", "->").key("foo").as("subkey"));


db.with("my_json_table", (db) =>
      db.selectNoFrom((eb) => toJson(exampleJson).as("my_example_json")),
    )
      .selectFrom("my_json_table")
      .select((eb) => eb.jsonPath<"my_example_json">().key("foo").as("subkey"));

It would be better if they had built-in support via:

sql.jsonPath<T extends object>

eb.val({ foo: { bar: 42 } }, '->>').key('foo')

Not only would these functions be helpful for existing projects, they would also be helpful for creating simpler examples for teaching kysely json utilities.

[koskimas]

eb.val({ foo: { bar: 42 } }, '->>').key('foo')

We'd have to keep adding the JSON functionality to every function if we did this.

We should rather pull out the json reference code to a separate helper. ref could still have it as it's a very common case. But we could also have something like:

eb.jsonRef(eb.val({ foo: { bar: 42 } }), '->>').key('foo')

Didn't give any thought to the naming here, but you get the gist.

eb.jsonRef (or whatever) would take any expression.

This

eb.ref('my_example_json', '->>').key('foo')

would just be a shortcut for this

eb.jsonRef(eb.ref('my_example_json'), '->>').key('foo')

Thoughts?

[igalklebanov]

Hey 👋

Makes sense.

I think eb.val(json) is blocked by #1130 - the under-the-hood serialization element of it. The gist of it was JSON.stringify by default, but allow custom logic override - e.g. running superjson, omitting/redacting things, etc.