I have a model User with a jsonb column email_triggers.

Email triggers is a series of key value pairs structured like {"email_one": "3/1/22", "email_two": "4/9/22", email_three: null}

I want to find all users where at least one of those values is null

I've been trying to convert the jsonb object into an array of values and then ask if null is included in that array

User.where("array[jsonb_each(email_triggers.value)] @> null")

Which is compiling as

SELECT "users".* FROM "users" WHERE (array[jsonb_each(email_triggers.value)] @> null) ORDER BY "users"."id" ASC LIMIT $1

but I am getting an undefined table error

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "email_triggers" (ActiveRecord::StatementInvalid) LINE 1: ...CT "users".* FROM "users" WHERE (array[jsonb_each(email_trig...

I'm not even sure this is the correct approach at this point, but the goal was to try to search against all values in the object to see if there are any null values.

Note: I also tried a_horse_with_no_name's suggested solution and it didn't return the desired result

CodePudding user response：

• The proper JSON representation of null is null not "nil".
• email_triggers.value means the column value of the table email_triggers.

You can use jsonb_each to turn the object into a table, join with it, find the values which are null (JSON null, not SQL null), and select only the distinct IDs.

select
  distinct id
from users, jsonb_each(email_triggers)
where value = 'null';

This can be made simpler if you store email_triggers as an array, and you might as well use a standard date format. For example: ["2022-03-01", "2022-04-09", null]

Now it's a simple @> (contains) search.

Users.where("email_triggers @> 'null'")

Finally, this will be faster and work better with Rails as a normal join table.

You can enforce the dates be stored with the proper date type and converted into Ruby Date objects.

class User << ApplicationRecord do
  has_many :email_triggers
end

# create_table(:email_triggers) do |t|
#   belongs_to :user, foreign_key: true
#   t.date :trigger_at, null: false
# end
class EmailTrigger << ApplicationRecord do
  belongs_to :user
end

The search is now a simple join.

Users.join(:email_triggers).where(trigger_at: nil)

However, this search is now not needed if you disallow nulls.