Service Bus Topic Subscription filter searching a value in array

Question

I've been searching about how to create a SQL filter for a topic subscription in Azure Service Bus searching a value in an array, but I didn't find anything and I don't know if it's possible?

I have a message like:

{
  "myArray":[ "value1", "value2", "value3" ],
  ...
}

If the array contains the value "value2", I want to send the message to appear for "subscription2", but I don't know how to create the filter.

I tried something like this, but it doesn't work:

'value2' in (myArray)

Is there any way to do it?

Thanks in advance.

You cannot apply a filter on the content/ body. Filters only work on the headers. You'd have to be creative by bringing the values into the header and use `LIKE` operator. — Sean Feldman, Mar 02 '23 at 17:23
Thank you! I was confused. I didn't notice that another method was adding properties to the header with some content from the message. The property name was the same, I thought it was applying the filter to the body. — jmmoyadev, Mar 03 '23 at 09:11

score 0 · Answer 1 · answered Mar 06 '23 at 07:59

Thanks to Sean Feldman for the comment.

Code to filter an array.

 string[] SBFilter = { "Topic1", "Topic2", "Topic3", "Topic4" };
       if (SBFilter.Contains("Topic3"))
        {
             Console.WriteLine("Topic Present");
        }
        else
         {
             Console.WriteLine("Topic Not Present");
         }

SQL Commmand to filter a table for a specific string from a column

select * from TABLE_Name where COLUMN_Name[1] like '%1%'

Code to search the Service Bus Topic Subscription.

 public class Program
        {
            static ServiceBusAdministrationClient adminClient;

            static readonly string connectionString = "ConnectionString";

            static readonly string topicName = "TopicName";

            static readonly string subscriptionAllOrders = "AllOrders";
            static readonly string subscriptionColorBlueSize10Orders = "ColorBlueSize10Orders";
            static readonly string subscriptionColorRed = "ColorRed";
            static readonly string subscriptionHighPriorityRedOrders = "HighPriorityRedOrders";

            public static async Task Main()
            {
                try
                {

                    Console.WriteLine("Creating the Service Bus Administration Client object");
                    adminClient = new ServiceBusAdministrationClient(connectionString);

                    Console.WriteLine($"Creating the topic {topicName}");
                    await adminClient.CreateTopicAsync(topicName);

                    Console.WriteLine($"Creating the subscription {subscriptionAllOrders} for the topic with a True filter ");
                    await adminClient.CreateSubscriptionAsync(
                            new CreateSubscriptionOptions(topicName, subscriptionAllOrders),
                            new CreateRuleOptions("AllOrders", new TrueRuleFilter()));


                    Console.WriteLine($"Creating the subscription {subscriptionColorBlueSize10Orders} with a SQL filter");
                    await adminClient.CreateSubscriptionAsync(
                            new CreateSubscriptionOptions(topicName, subscriptionColorBlueSize10Orders),
                            new CreateRuleOptions("BlueSize10Orders", new SqlRuleFilter("color='blue' AND quantity=10")));

                    Console.WriteLine($"Creating the subscription {subscriptionColorRed} with a SQL filter");
                    await adminClient.CreateSubscriptionAsync(topicName, subscriptionColorRed);
                    await adminClient.DeleteRuleAsync(topicName, subscriptionColorRed, "$Default");
                    await adminClient.CreateRuleAsync(topicName, subscriptionColorRed, new CreateRuleOptions
                    {
                        Name = "RedOrdersWithAction",
                        Filter = new SqlRuleFilter("user.color='red'"),
                        Action = new SqlRuleAction("SET quantity = quantity / 2; REMOVE priority;SET sys.CorrelationId = 'low';")

                    }
                    );

                    Console.WriteLine($"Creating the subscription {subscriptionHighPriorityRedOrders} with a correlation filter");
                    await adminClient.CreateSubscriptionAsync(
                            new CreateSubscriptionOptions(topicName, subscriptionHighPriorityRedOrders),
                            new CreateRuleOptions("HighPriorityRedOrders", new CorrelationRuleFilter() { Subject = "red", CorrelationId = "high" }));

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
            }
        }

Output after filtering the Service bus topic using code. enter image description here

Service Bus Topic

enter image description here

SQL Filter in Service Bus Subscription

enter image description here

Reference taken from MSDoc.

Service Bus Topic Subscription filter searching a value in array

1 Answers1