[BUG]: json_array_length not working with multiple subqueries

[johanneskares]

What version of drizzle-orm are you using?

0.26.3

What version of drizzle-kit are you using?

No response

Describe the Bug

It's no longer possible to return multiple subqueries when using json_array_length.

In a previous bug report, there was another issue with multiple subqueries: https://github.com/drizzle-team/drizzle-orm/issues/599

After the fix, now I'm getting an error when using

where: (table) => sql`json_array_length(${table.projects}) > 0`

To replicate using the example from the original bug report and change it to:

db.query.tenant.findFirst({
  where: eq(tenant.id, "..."),
  with: {
    users: true,
    projects: true,
  },
  where: (table) => sql`json_array_length(${table.projects}) > 0`,
});

I'm getting: function json_array_length(text) does not exist. It works using 0.26.0.

Expected behavior

No error

Environment & setup

No response

[dankochetov]

True, the query was changed a bit. You can use cast(<column> as json) to work around that.

[johanneskares]

fantastic, good to know

sql`json_array_length(cast (${table.projects} as json)) > 0`,

did the trick