Oracle SQL Graph connector - columns shows comma separated filter values and are also duplicated

[mw-new-test]

Version used
4.9.1

Describe the bug
We have Oracle data source which we have added to the MS Search index. The Oracle data source is basically a SQL view consisting of columns and rows/results that are indexed. We have certain columns in the SQL view. Column1 - Products, Column2 - Topics
Each row/result in the SQL Table/view can be associated with multiple products and multiple topics.
Ex of SQL View:

The column Products and Topics have comma separated values (because they are of type text/VARCHAR)
When these columns are populated in PnP Modern search filters webpart, they show up as text.
There is no way to split the comma separated values and only populate unique values and then perform a CONTAINS refinement instead of EQUALS search with the Filters.
Example:
Filter values for Products should be:

• ABC
• XYZ
• JKL
  Filter values for Topics should be:
• PQR
• EFG
• MNO

It would be good to know if filters can be split by values if they consist of comma separated values and show only unique ones.

To Reproduce
Detailed steps to reproduce the behavior:

1. Add Oracle/SQL data source (Table/View) to MS Search index with columns that are Refinable
2. Configure PnP Search webparts. Use Microsoft Search as data source. Use content source defined in Step 1 in the webpart properties of Search results
3. Add columns for Filters in the Search Filter webpart
4. See issue of comma separated values and duplicates in the Filters

Expected behavior
There should be a way to split the comma separated values as individual values and eliminate duplicates within such Filters

Screenshots

Desktop (please complete the following information):

• Chrome, Edge

Additional context
Add any other context about the problem here.

[wobba]

You would have to develop more logic to the refiner web part to allow it to split values on a character at the data layer, or create a custom refinement handlebar template which splits the values for you. You may be able to get the SQL used for the values to split as well and return multiple rows back?

[mw-new-test]

I am able to create a custom handlebar and split the filter values and return only unique values.
But the results webpart getting results from the Graph API still consists of comma separated values.
Any idea on how to do a contains search between the values returned by the Filter webpart and the values returned on the results webpart?

[mw-new-test]

We cannot get SQL to split the values and return multiple rows back. Splitting it in SQL would imply duplication of ID/Primary key and duplication of results in the Search results webpart for each Product and Topics the row/result is associated with.

[wobba]

You can add a feature in the web part, or even in the SQL connector you have to splitt the data like you want. Not in an handlebar extension.

[mw-new-test]

Thanks @wobba. But still confused. Could you please give an example? We do not have control on the Oracle/SQL connector (we are using the Microsoft graph connector/agent here and the only option is the provide a Select statement to a table/view)

[wobba]

@mw-new-test I misread it a bit and thought you had a custom provider in the web parts. Using Microsoft Search API for a graph connector.. got it. Which means you ether modify the web part, or you can create a web component to fix your data and use that in a custom refiner template. You need a developer with some SPFx experience to get going.