Kysely•2y 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•2y ago

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

LunaOP•2y ago

pg (postgres) and I use newest Kysely

Igal•2y 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

LunaOP•2y ago

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

LunaOP•2y ago

LunaOP•2y ago

nvm was just vs code having it's moment

Igal•2y ago

did it work?

LunaOP•2y 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

LunaOP•2y ago

If I comment the .orderBy away it works

Igal•2y ago

what postgres version?

LunaOP•2y ago

pg (npm package) or postgres on the server?

Igal•2y ago

server

LunaOP•2y 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•2y 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...

LunaOP•2y ago

@Igal I found the issue, in your example you used the -> operator but it's ->>, it works now

Igal•2y ago

it should work with -> too. how's the column defined? json or jsonb?

LunaOP•2y ago

.addColumn("activity", "json", (col)

Igal•2y ago

bingo, when changing the data type to json in tests it errors for order by with: error: could not identify an ordering operator for type json and works again when changing to ->>.

LunaOP•2y ago

I already paniced because I didn't know what I should do and just randomly was scrolling through the test file and found the ->>, tried it and worked lol thanks anyway

LunaOP•2y ago

https://c.lunish.nl/r/4hU8WT.mp4 I am kind of lost here, why are the activity.messages higher on page 1 than on page 0? (each page only adds an offset [page] * 10)

koskimas•2y ago

->> casts the thing you access to a string. The sorting is done using a stringified number. You probably need to explicitly cast to integer

LunaOP•2y ago

ah okey, how do I do that (lol) (@Igal^)

Solution

Igal•2y ago

try something like:

const rows = await kysely
  .selectFrom("member")
  .selectAll()
  .orderBy((eb) => sql`cast(${eb.ref("activity", "->>").key("messages")} as integer)`, "desc")
  .limit(10)
  .execute()

const rows = await kysely
  .selectFrom("member")
  .selectAll()
  .orderBy((eb) => sql`cast(${eb.ref("activity", "->>").key("messages")} as integer)`, "desc")
  .limit(10)
  .execute()

LunaOP•2y ago

yey that seems to work!! thank you

Gaming

Programming

Using `.orderBy` in JSON

Did you find this page helpful?