K
Kysely•10mo ago
tzezar

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....
Jump to solution
3 Replies
Solution
koskimas
koskimas•10mo 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
tzezarOP•10mo 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•10mo ago
You're welcome! The playground is awesome. It's written and maintained by @wirekang https://github.com/wirekang/kysely-playground

Did you find this page helpful?