Kysely Query with array of "From" with raw SQL

Question

I am using Kysely query builder (with Postgres), However applying Kysely equivalent for FROM X, Y does not seem to work:

E.g.

SELECT *
FROM
  products,
    jsonb_array_elements(products.properties#>'{someProperty}') as search_item

where cast(value as integer)=2

I tried look for the equivalent and saw in the source code documentation: /node_modules/kysely/dist/esm/query-creator.d.ts:

import { sql } from 'kysely'

const items = await db.selectFrom([
    'person as p',
    db.selectFrom('pet').select('pet.species').as('a'),
    sql<{ one: number }>`(select 1 as one)`.as('q')
  ])
  .select(['p.id', 'a.species', 'q.one'])
  .execute()

The generated SQL (PostgreSQL):

select "p".id, "a"."species", "q"."one"
from
  "person" as "p",
  (select "pet"."species" from "pet") as a,
  (select 1 as one) as "q"

So I used the syntax utilizing the sql object like the 3rd select argument as the following:

const res2 = await db.selectFrom([
    'products',
    sql<{ someProperty: number }>`(jsonb_array_elements(products.properties#>'{someProperty}')`.as('search_item')
  ]).select(['products.properties', 'search_item']).execute();

However getting an error:

error: Uncaught (in promise) PostgresError: syntax error at end of input
          error = new PostgresError(parseNoticeMessage(current_message));
                  ^
    at Connection.#simpleQuery (https://deno.land/x/[email protected]/connection/connection.ts:700:19)
    at eventLoopTick (ext:core/01_core.js:183:11)
    at async Connection.query (https://deno.land/x/[email protected]/connection/connection.ts:949:16)
    at async PoolClient.#executeQuery (https://deno.land/x/[email protected]/client.ts:245:12)
    at async PoolClient.queryObject (https://deno.land/x/[email protected]/client.ts:433:12)
    at async PostgreSQLDriverDatabaseConnection.executeQuery (https://deno.land/x/[email protected]/src/PostgreSQLDriverDatabaseConnection.ts:14:12)
    at async https://esm.sh/v133/[email protected]/denonext/kysely.mjs:3:50936
    at async Or.provideConnection (https://esm.sh/v133/[email protected]/denonext/kysely.mjs:3:86791)
    at async t.executeQuery (https://esm.sh/v133/[email protected]/denonext/kysely.mjs:3:50891)
    at async t.execute (https://esm.sh/v133/[email protected]/denonext/kysely.mjs:3:58522)

Any idea? It seems equivalent to the docs

Answer 1

Eventually there was an extra ( which translated into invalid query. The correct term is:

sql<{ bestPlayerCount: number }>`jsonb_array_elements(products.properties#>'{someProperty}')`.as('search_item')

I found it via searching for where deno store the cached file:

~/.deno/bin/deno info --json https://deno.land/x/[email protected]/mod.ts | grep connection.ts -B 10

Modified the file to print the generated query before executing it, while also removing the matching file postgres@../connection/connection from deno.lock so it won't fail due to checksum mismatch.