Kysely•2mo ago

how to decode a point to `{x: number, y: number}` when selected inside `jsonBuildObject`?

when i select a column of type point the basic way i get it decoded to {x: number, y: number}, but when i wrap it in jsonBuildObject i get it as the raw string. example:

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: eb.ref('event.location'),
    }).as('wrapperObject'),
  ]);

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: eb.ref('event.location'),
    }).as('wrapperObject'),
  ]);

[
  {
    "location": {
      "x": 4.9,
      "y": 52.378
    },
    "wrapperObject": {
      "location": "(4.9,52.378)"
    }
  }
]

[
  {
    "location": {
      "x": 4.9,
      "y": 52.378
    },
    "wrapperObject": {
      "location": "(4.9,52.378)"
    }
  }
]

Solution:

The only way to detect those columns inside JSON is regex or similar. There's no type info. In JSON, that's just a string.

Jump to solution

5 Replies

lorentz•2mo ago

i fail to find the source code for where the point decoding is done in the basic select case

koskimas•2mo ago

Kysely doesn't decode anything. It never touches the output in any way whatsoever. It's the underlying driver that does the conversions. In this case it's converting the point to JSON and for some reason that's what it does. You're using postgres? Then it's pg that's responsible for the conversions. But in this particular case the conversion is already done in the DB since it's the DB that converts the column into JSON. You could use something like this

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: jsonPoint(eb.ref('event.location')),
    }).as('wrapperObject'),
  ]);

function jsonPoint(point: Expression<Point>) {
  return jsonBuildObject({
    x: getX(point),
    y: getY(point),
  })
}

function getX(point: Expression<Point>) {
  return sql<number>`${point}[0]`
}

function getY(point: Expression<Point>) {
  return sql<number>`${point}[1]`
}

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: jsonPoint(eb.ref('event.location')),
    }).as('wrapperObject'),
  ]);

function jsonPoint(point: Expression<Point>) {
  return jsonBuildObject({
    x: getX(point),
    y: getY(point),
  })
}

function getX(point: Expression<Point>) {
  return sql<number>`${point}[0]`
}

function getY(point: Expression<Point>) {
  return sql<number>`${point}[1]`
}

Point being something like {x: number, y: number} or whatever you've typed your points to be. https://kysely.dev/docs/recipes/data-types

lorentz•2mo ago

ah ok, thanks! in my case the point column is nullable, making it more complicated i guess 🤔 if i were to do it in js-land instead, is a plugin with transformResult a reasonable approach?

Solution

koskimas•2mo ago

The only way to detect those columns inside JSON is regex or similar. There's no type info. In JSON, that's just a string.

koskimas•2mo ago

But if that's ok, then a plugin is a good solution.

Gaming

Programming

how to decode a point to `{x: number, y: number}` when selected inside `jsonBuildObject`?