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?

Girl with BlåhajOP•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

Girl with BlåhajOP•2y ago

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

Girl with BlåhajOP•2y ago

Girl with BlåhajOP•2y ago

nvm was just vs code having it's moment

Igal•2y ago

did it work?

Girl with BlåhajOP•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

Girl with BlåhajOP•2y ago

If I comment the .orderBy away it works

Igal•2y ago

what postgres version?

Girl with BlåhajOP•2y ago

pg (npm package) or postgres on the server?

Igal•2y ago

server

Girl with BlåhajOP•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...

Girl with BlåhajOP•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?

Girl with BlåhajOP•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 ->>.

Girl with BlåhajOP•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

Girl with BlåhajOP•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

Girl with BlåhajOP•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()

Girl with BlåhajOP•2y ago

yey that seems to work!! thank you

Gaming

Programming

Using `.orderBy` in JSON

Did you find this page helpful?