K
Kyselytzezar

newbie need help with json_build_object

Hi! Just starting with kysely and encountered problem I cant solve. I want to build raw query with kysely builder... 
  JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
  JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
this part gives me trouble. I would be grateful for your help 
        sql`
        SELECT 
            sku.id,
            JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
        FROM sku
        LEFT JOIN unit ON unit.id = sku.stock_unit_id
        LIMIT 100;
    `


    let results = await kyselydb
        .selectFrom('sku')
        .leftJoin('unit as u', 'u.id', 'sku.stock_unit_id')
        .selectAll('sku')
        .select((eb) => [
            jsonObjectFrom(

            ).as('unit')
        ])
        .limit(10)
        .execute()
        sql`
        SELECT 
            sku.id,
            JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
        FROM sku
        LEFT JOIN unit ON unit.id = sku.stock_unit_id
        LIMIT 100;
    `


    let results = await kyselydb
        .selectFrom('sku')
        .leftJoin('unit as u', 'u.id', 'sku.stock_unit_id')
        .selectAll('sku')
        .select((eb) => [
            jsonObjectFrom(

            ).as('unit')
        ])
        .limit(10)
        .execute()
Solution:
Here you go https://kyse.link/DqXNv Note that the properties are nullable in the selected unit object because you're using left join. The types are correct. If you are sure there's a unit for each sku then use innerJoin instead....
Solution
koskimas
koskimas•39d ago
Here you go https://kyse.link/DqXNv Note that the properties are nullable in the selected unit object because you're using left join. The types are correct. If you are sure there's a unit for each sku then use innerJoin instead.
tzezar
tzezar•39d ago
aaa okey, so this is how it's done. Thanks a lot for the quick reply! And for clearing up the other doubt, regarding nullable values. Cool editor btw
koskimas
koskimas•39d ago
You're welcome! The playground is awesome. It's written and maintained by @wirekang https://github.com/wirekang/kysely-playground
Want results from more Discord servers?
Add your server
More Posts
Solved: Exporting query builder classesI saw this PR https://github.com/kysely-org/kysely/pull/763 where you guys have decided not to exporbuilding kysely makes the package size very largehi. i'd be more than happy to figure this out because i like kysely a lot more than drizzle. the thiKysely setup in monolith APIHello, we have a basic http server with a basic router and a PostgreSQL database. We're wondering whIs it possible to get the total count while fetching rows in a single query?I have a query which looks like this: ```typescript const { items, count } = await db .selectFrom(Separating results of join into objects of each typeHey guys, I'm curious if anyone knows a way to unmerge the results of a join into objects of both tySnippet compilationHi, is it somehow possible to get only part of the compiled code to use it as snippet in ORM? Or vicCreating an 'enum' type columnHi all, Trying to re-create this MySQL in Kysely ``` ... CREATE TABLE ...( ... members ENUM('femalError when destructuring QueryCreator in withRecursiveI'm new to Kysely and attempting to migrate a Next app from prisma to kysely. I've generated types, Is `sql.join` the best way to concatenate sql templates?I have some business logic that incrementally builds up a sql WHERE query as a string. It looks likeMultiple calls to values in insert statement?I just had a production bug where I was calling `.values` multiple times on a query object. I've fix0.27.3 is out. Didn't have time to write0.27.3 is out. Didn't have time to write release notes yet since it was a hotfix to support typescriusing pgvector with KyselyFollowing the example here: https://github.com/pgvector/pgvector-node?darkschemeovr=1#kysely But I