One of the problems that you can encounter (especially) when using the pub/sub model in Service Bus is the issue of noise. The problem being that you subscribe to a topic, and you get everything going to that topic. Let’s take the following example:

Topic: SalesOrders

Messages: New Order, Cancel Order, Amend Order

Now, imagine that your application deals with cancelling orders, and so your subscription is only interested in the Cancel Order messages. However, your subscription fills up with New Order and Amend Order messages.

In this case, you have the following options:

• You can ignore the messages that you don’t care about - you can do this by reading all the messages, and then, for the ones that you’re not interested in, abandoning the message. However, this creates two problems: whilst the message is abandoned, it will be returned to the subscription, and potentially picked up again. Eventually, if you pick up the message enough times, the message will dead letter; but then you need to deal with the fact that the dead letter queue will continually get bigger.
• You can explicitly dead letter the message - this avoids the issue where you may pick it up multiple times, although it does restrict the scenario where more than one process is listening to the same subscription.
• You could consume the messages that you’re not interested in, but discard the result.
• Finally, you can add a filter to the subscription - which is the subject of this article.

SQL Filters

Before we get into what SQL filters are, let’s just kill the elephant in the room: this is called a SQL filter because the language that you use is a SQL like language. It’s not really SQL - it just sort of feels a bit like it.

To set up a SQL Filter, you can use the SDK, some kind of IaC (Terraform for example), or you can simply edit the subscription in the portal (or in Service Bus Explorer).

Let’s see how you might add one:

You can see the statement, which is filtering the messages:

MyProperty LIKE 'O%'

Let’s talk a bit about exactly what we’re filtering here.

What can you Filter?

To my slight surprise, you can not filter the content of the message. For example, if you have a JSON document in your message, you can’t filter on the contents. You can filter on message properties. The filter above will capture this message:

However, you can also filter on the system properties; for example:

sys.enqueuedSequenceNumber >= 300

Summary

This approach obviously puts less overhead on the processing of messages; however, you do still have the potential issue that the messages that you’re not interested in will pile up.