Kysely•4mo ago

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" } }
}

{
  "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';

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();

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';

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

3 Replies

OverHashOP•4mo ago

I saw that there is a jsonPath function which I played around for a bit, but I wasn't able to tell jsonPath to use my sess column which caused it to fail.

Solution

koskimas•4mo ago

Just use ->> for all of them?

OverHashOP•4mo ago

:o, I have no idea why I didn't think that would work I was thinking postgresql would treat the passport object as a text rather than continue to follow it as json thanks 😄

Gaming

Programming

JSON Object Traversal