SearchKit: search for Cases with criteria relating to a custom field

Question

In SearchKit, I am trying to search for Cases which have a specific value assigned to a custom field ("Case Subtype"), or for which the custom field is not populated.

I don't seem to be able to construct the query to satisfy both conditions, though I have successfully created separate queries to deliver each condition.

It seems to me that it might be down to a lack of understanding of the relationship between the Cases table and an associated Case custom fields table, albeit that the custom field I am operating on is visible for selection in the query without joining another table.

Here is the query structure I have tried (and I have tried variations of it too), which yields no results:

{
  "version": 4,
  "select": [
    "Case_CaseContact_Contact_01.sort_name",
    "subject",
    "case_type_id:label",
    "case_custom.case_custom_subject:label",
    "status_id:label",
    "start_date",
    "Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.membership_type_id:label",
    "Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.status_id:label",
    "Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.display_name",
    "Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.near_contact_id.display_name",
    "Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.membership_type_id:label",
    "Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.status_id:label",
    "Case_CaseActivity_Activity_01.subject",
    "Case_CaseActivity_Activity_01.activity_date_time"
  ],
  "orderBy": [],
  "where": [
    [
      "case_type_id:name",
      "=",
      "certified_membership"
    ],
    [
      "ISNULL(case_custom.case_custom_subject:name)",
      "=",
      true
    ],
    [
      "OR",
      [
        [
          "case_custom.case_custom_subject:name",
          "IN",
          [
            "Uncertified_participant",
            "Certified_participant"
          ]
        ]
      ]
    ]

Answer 1

It looks like you are AND'ing the two conditions so it's never true. This seems to work for me:

So the WHERE clause is like:

"where": [
    [
      "OR",
      [
        [
          "cas.something:name",
          "IS EMPTY"
        ],
        [
          "cas.something:name",
          "=",
          "two"
        ]
      ]
    ]
  ],