Ребят, все огонь, методом тыка выявил верный синтаксис :)
@Query("select campaign from CampaignEntity campaign " +
"left join fetch campaign.attachedClient attachedClient " +
"left join fetch campaign.clients clients " +
"where " +
"(campaign.type = 'event' AND lower(attachedClient.name) like CONCAT('%',lower(?1),'%')) OR " +
"(campaign.type = 'system' AND campaign.clientsRelation = 'all') OR " +
"(campaign.type = 'system' AND campaign.clientsRelation = 'some' AND lower(clients.name) like CONCAT('%',lower(?1),'%')) OR " +
"(campaign.type = 'system' AND campaign.clientsRelation = 'all_except' AND lower(clients.name) not like CONCAT('%',lower(?1),'%'))")
Set<CampaignEntity> findByClientName(String name);