[ask] how to query json field exist/not exist

[bingtsingw]

This is my orm query:

const data = await db.user
  .where({
    verification: {
      jsonPath: ['$.verified', '=', null],
    },
  })
  .where({
    verification: {
      jsonPath: ['$.idCardNo', '<>', null],
    },
  })
  .select('id');

which generates this db query: SELECT "user"."id" FROM "user" WHERE jsonb_path_query_first("user"."verification", '$.verified') #>> '{}' = $1 AND jsonb_path_query_first("user"."verification", '$.idCardNo') #>> '{}' <> $2 [NULL, NULL]

But it not work as expected.

I find this query is working SELECT "user"."id" FROM "user" WHERE ("user"."verification"->'verified') is null AND ("user"."verification"->'idCardNo') is not null, but I don't know how to write it in orchid orm

[bingtsingw]

I find a way with raw sql can achieve:

const data = await db.user
  .where(db.user.sql`"verification"->'verified' is null`)
  .where(db.user.sql`"verification"->'idCardNo' is not null`)
  .select('id');

Any other way to do this query?

[romeerez]

I'm sorry for the late response, gladly you found a workaround with raw SQL.

Published fix, now this should be fine:

const data = await db.user
  .where({
    verification: {
      jsonPath: ['$.verified', 'is', null],
    },
  })
  .where({
    verification: {
      jsonPath: ['$.idCardNo', 'is not', null],
    },
  })
  .select('id');

Previously this was failing with SQL syntax error because null was passed as a query variable, and apparently Postgres doesn't support this.