Kysely•14mo ago

Using `.orderBy` in JSON

Hey, I have a schema that roughly looks like this

export interface Member {
    id: string;

    activity: {
        messages: number;
    };
}

export interface Member {
    id: string;

    activity: {
        messages: number;
    };
}

and I want to query the db like that it gives me the top 10 Members with the highest activity.messages count. I figured out how I can orderBy but not inside of a json.

Solution:

try something like: ```ts const rows = await kysely .selectFrom("member")...

Jump to solution

25 Replies

Igal•14mo ago

Hey 👋 Are you using Kysely 0.26.x? what dialect are you using?

Blåhaj•14mo ago

pg (postgres) and I use newest Kysely

Igal•14mo ago

In 0.26.x we've introduced type-safe JSON traversal, it's not documented yet in kysely.dev site, only in API docs and in the IDE. Lemme know if this works for you: https://kyse.link/?p=s&i=hKiV5qAL1BLLi3tvlwEq

const rows = await kysely
  .selectFrom("member")
  .selectAll()
  .orderBy((eb) => eb.ref("activity", "->").key("messages"), "desc")
  .limit(10)
  .execute()

const rows = await kysely
  .selectFrom("member")
  .selectAll()
  .orderBy((eb) => eb.ref("activity", "->").key("messages"), "desc")
  .limit(10)
  .execute()

SELECT
  *
FROM
  "member"
ORDER BY
  "activity" -> 'messages' DESC
LIMIT
  $1

SELECT
  *
FROM
  "member"
ORDER BY
  "activity" -> 'messages' DESC
LIMIT
  $1

Blåhaj•14mo ago

will try when I’m home, than you :)

Blåhaj•14mo ago

Blåhaj•14mo ago

nvm was just vs code having it's moment

Igal•14mo ago

did it work?

Blåhaj•14mo ago

process crashes with

node:internal/streams/writable:303
      throw new ERR_UNKNOWN_ENCODING(encoding);
      ^

TypeError [ERR_UNKNOWN_ENCODING]: Unknown encoding: uncaughtException
    at new NodeError (node:internal/errors:399:5)
    at _write (node:internal/streams/writable:303:13)
    at Writable.write (node:internal/streams/writable:344:10)
    at process.emit (node:events:511:28)
    at process._fatalException (node:internal/process/execution:159:25) {
  code: 'ERR_UNKNOWN_ENCODING'
}

Node.js v20.1.0

node:internal/streams/writable:303
      throw new ERR_UNKNOWN_ENCODING(encoding);
      ^

TypeError [ERR_UNKNOWN_ENCODING]: Unknown encoding: uncaughtException
    at new NodeError (node:internal/errors:399:5)
    at _write (node:internal/streams/writable:303:13)
    at Writable.write (node:internal/streams/writable:344:10)
    at process.emit (node:events:511:28)
    at process._fatalException (node:internal/process/execution:159:25) {
  code: 'ERR_UNKNOWN_ENCODING'
}

Node.js v20.1.0

Blåhaj•14mo ago

If I comment the .orderBy away it works

Igal•14mo ago

what postgres version?

Blåhaj•14mo ago

pg (npm package) or postgres on the server?

Igal•14mo ago

server

Blåhaj•14mo ago

PostgreSQL 14.8 (Ubuntu 14.8-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit

Igal•14mo ago

I've added some test cases for json traversal in order by clauses https://github.com/kysely-org/kysely/pull/621 and everything seems to be OK. we use pg 8.11.0 and postgres 15.x images.

GitHub

add json traversal order by test cases. by igalklebanov · Pull Requ...

Gaming

Programming

Using `.orderBy` in JSON