我现在在用Ruby on Rails搭配 GoodJob 来发送延迟邮件。

我想通过查询找到这些邮件，在事件取消时把它们删除，但我之前从没真正接触过jsonb，一直没能把查询写对。

下面是 serialized_params 的信息，我需要通过 "ReminderMailer" 和 "event_id" 来识别：

{
  "job_id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e",
  "locale": "en",
  "priority": null,
  "timezone": "Mountain Time (US & Canada)",
  "arguments": [
    "ReminderMailer",
    "inspection_created",
    "deliver_now",
    {
      "args": [],
      "params": {
        "event_id": 2480,
        "recipient": [
          "[email protected]",
          "Jesse"
        ],
        "_aj_symbol_keys": [
          "event_id",
          "recipient"
        ]
      },
      "_aj_ruby2_keywords": [
        "params",
        "args"
      ]
    }
  ],
  "job_class": "ActionMailer::MailDeliveryJob",
  "executions": 0,
  "queue_name": "default",
  "enqueued_at": "2026-05-12T14:02:41.494322527Z",
  "scheduled_at": "2026-05-13T18:00:00.000000000Z",
  "provider_job_id": null,
  "exception_executions": {},
  "_good_job": {
    "id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e",
    "queue_name": "default",
    "priority": 0,
    "scheduled_at": "2026-05-13 12:00:00 -0600",
    "performed_at": null,
    "finished_at": null,
    "error": null,
    "created_at": "2026-05-12 08:02:41 -0600",
    "updated_at": "2026-05-12 08:02:41 -0600",
    "active_job_id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e",
    "concurrency_key": null,
    "cron_key": null,
    "retried_good_job_id": null,
    "cron_at": null,
    "batch_id": null,
    "batch_callback_id": null,
    "is_discrete": null,
    "executions_count": null,
    "job_class": "ActionMailer::MailDeliveryJob",
    "error_event": null,
    "labels": null,
    "locked_by_id": null,
    "locked_at": null,
    "lock_type": null
  }
}

我已经尝试过，但没有结果

GoodJob::Job.where("serialized_params->arguments->>0 = ?", "ReminderMailer")

解决方案

我猜是你在 arguments 周围缺少单引号：

GoodJob::Job.where("serialized_params->'arguments'->>0 = ?", "ReminderMailer")

没有它们，你要么忽略了在你的表中存在一个名为 arguments 的列，从而让这段查询使用该列的值，而不是实际导航到 $.arguments 的jsonpath；要么你捕获并忽略/处理错误，导致错误信息没有指出缺失： demo at db<>fiddle

create table job(serialized_params)as values(jsonb '{"job_id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e", "locale": "en", "priority": null, "timezone": "Mountain Time (US & Canada)", "_good_job": {"id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e", "error": null, "labels": null, "cron_at": null, "batch_id": null, "cron_key": null, "priority": 0, "job_class": "ActionMailer::MailDeliveryJob", "lock_type": null, "locked_at": null, "created_at": "2026-05-12 08:02:41 -0600", "queue_name": "default", "updated_at": "2026-05-12 08:02:41 -0600", "error_event": null, "finished_at": null, "is_discrete": null, "locked_by_id": null, "performed_at": null, "scheduled_at": "2026-05-13 12:00:00 -0600", "active_job_id": "9522f8a0-7eca-4682-a6e8-ef8771e5b86e", "concurrency_key": null, "executions_count": null, "batch_callback_id": null, "retried_good_job_id": null}, "arguments": ["ReminderMailer", "inspection_created", "deliver_now", {"args": [], "params": {"event_id": 2480, "recipient": ["[email protected]", "Jesse"], "_aj_symbol_keys": ["event_id", "recipient"]}, "_aj_ruby2_keywords": ["params", "args"]}], "job_class": "ActionMailer::MailDeliveryJob", "executions": 0, "queue_name": "default", "enqueued_at": "2026-05-12T14:02:41.494322527Z", "scheduled_at": "2026-05-13T18:00:00.000000000Z", "provider_job_id": null, "exception_executions": {}}');

select *
from job
where serialized_params->arguments->>0 = 'ReminderMailer';

none ERROR: column "arguments" does not exist LINE 3: where serialized_params->arguments->>0 = 'ReminderMailer'; ^

如果你加上引号：

select serialized_params->'arguments'->>0 as q
     , serialized_params
from job;

如果存在名为 arguments 的列，它会掩盖问题：

alter table job 
  add column arguments text default 'this_key_does_not_exist_in_serialized_params';

select *
from job
where serialized_params->arguments->>0 = 'ReminderMailer';

它返回一个空结果集，因为它等价于 serialized_params->'this_key_does_not_exist_in_serialized_params'->>0，指向无处可去的地方。