What is the best way to join with JSONB arrays with Drizzle?
Hi guys,
I'm trying to migrate the following query to Drizzle:
SELECT "mutzar"."misparzihuylakoach",
"mutzar"."sugmutzar",
"mutzar"."shemyatzran",
"heshbonopolisas" ->> 'MISPAR_POLISA_O_HESHBON'
FROM "mutzar"
LEFT JOIN Jsonb_array_elements("data" -> 'HeshbonotOPolisot' ->
'HeshbonOPolisa'
) AS
"heshbonOPolisas"
ON TRUE
WHERE "mutzar"."misparzihuylakoach" = $1 -- params: ["123123123"]
I've successfully migrated it to:
const result = await db
.select({
misparZihuyLakoach: mutzar.misparZihuyLakoach,
sugMutzar: mutzar.sugMutzar,
shemYatzran: mutzar.shemYatzran,
misparPolisaOHeshbon: sql
"heshbonOPolisas" ->> 'MISPAR_POLISA_O_HESHBON',
})
.from(mutzar)
.leftJoin(
sqljsonb_array_elements("data"->'HeshbonotOPolisot'->'HeshbonOPolisa') as "heshbonOPolisas",
eq(sqlTRUE, sqlTRUE)
)
.where(eq(mutzar.misparZihuyLakoach, '123123123'));
I've tried following the docs and using const heshbonOPolisas = alias(sqljsonb_array_elements("data"->'HeshbonotOPolisot'->'HeshbonOPolisa'), "heshbonOPolisa") but got a type error
What is the best way to join with JSONB arrays with Drizzle?0 Replies