Postgres ARRAY and sql template proper syntax

Hello there ! I have a where clause in a query that looks like this :

        .where(
          sql`
            EXISTS (
              SELECT 1
              FROM unnest(${S.user.interests}) AS interest
              JOIN unnest(ARRAY[${courseTagsString}]) AS target_interest ON LOWER(interest) = LOWER(target_interest)
            )
          `
        )

        .where(
          sql`
            EXISTS (
              SELECT 1
              FROM unnest(${S.user.interests}) AS interest
              JOIN unnest(ARRAY[${courseTagsString}]) AS target_interest ON LOWER(interest) = LOWER(target_interest)
            )
          `
        )

I can't figure out a nice way to pass something to that array. AFAIK the only think that works is to .join() the string and manually do :

JOIN unnest(ARRAY${sql.raw(`[${formattedString}]`)})

JOIN unnest(ARRAY${sql.raw(`[${formattedString}]`)})

Is there a nicer way ? If that's the way, I'm happy to add that to the docs, because it took a lot of fiddling to figure out.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Postgres ARRAY and sql template proper syntax