Kysely•2y ago

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

unit

object because you're using left join. The types are correct. If you are sure there's a

unit

unit

for each

sku

sku

then use

innerJoin

innerJoin

instead.

Jump to solution

Solution

koskimas•4/9/24, 12:01 PM

Here you go https://kyse.link/DqXNv

Note that the properties are nullable in the selected

unit

unit

object because you're using left join. The types are correct. If you are sure there's a

unit

unit

for each

sku

sku

then use

innerJoin

innerJoin

instead.

Kkoskimas Here you go https://kyse.link/DqXNv Note that the properties are nullable in th...

tzezarOP•4/9/24, 12:09 PM

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•4/9/24, 12:25 PM

You're welcome! The playground is awesome. It's written and maintained by @wirekang https://github.com/wirekang/kysely-playground