KyselyK
Kysely•16mo ago
OverHash

JSON Object Traversal

Hi everyone, new user of Kysely.
I have the following json object inside a sess column:
{
  "cookie": {
    "originalMaxAge": 2592000000,
    "expires": "2024-09-28T04:18:12.565Z",
    "secure": false,
    "httpOnly": true,
    "path": "/",
    "sameSite": "lax"
  },
  "passport": { "user": { "user_id": "45c718f8-a3c7-4fb4-b50a-44c8fec2fea1" } }
}

I'm trying to write a type-safe Kysely query to traverse this. Essentially, I want a Postgresql query that looks like this:
select * from "sessions" where "sess"->'passport'->'user'->>'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';

I'm almost there, however, I'm running into some issue getting thjat last ->> part. Here is what I have so far:
await db
  .selectFrom("sessions")
  .where(
    ({ ref }) =>
      ref("sess", "->").key("passport").key("user").key("user_id"),
    "=",
    userId,
  )
  .executeTakeFirstOrThrow();

this, unfortunately, generates a query like this:
select * from "sessions" where "sess"->'passport'->'user'->'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';
(notice that the last user -> user_id is not a ->>, as it should be) which causes postgresql to fail.

How am I supposed to tell Kysely to use ->> on the last step there?
Solution
Just use ->> for all of them?
Jump to solution
Was this page helpful?