Filtering support for nested properties in JSON strings

[talalekk]

trafficstars

I am trying to use the OData lib for a very specific use case, but I am not sure if there is a native support for that. I have prepared a small example to better illustrate the use case:

1. Suppose we have the following table and records in the database:

Id	Name	CustomMetadata
0	"first"	"{ "CustomPropA": "CustomValA" }"
1	"second"	"{ "CustomPropB": "CustomValB" }"
2	"third"	"{ "CustomPropA": "CustomValA", "CustomPropC": "CustomValC" }"

2. CustomMetadata is stored in the DB as a JSON string of user-defined properties without any fixed structure.

Is it possible to enable the filtering for properties which are part of the CustomMetadata field. For example:

• $filter=CustomMetadata.CustomPropA eq 'CustomValA' should return the first and last records.
• $filter=CustomMetadata.NewProp eq 'NewVal' should not return any entities.

I went through some of the documentation, but I could not find anything that would suggest support for this case. I found this issue which seems to be somewhat similar, but a follow-up answer is missing.

I am guessing that this might be out-of-scope for the current version of the package, but wanted to hear a second opinion on it.

[xuzhg]

How can you pass "$filter=CustomMetadata.CustomPropA eq 'CustomValA'" to the DB and execute the filter clause?

You let the DB to parse the JSON and compare the property name and property value? It's weird.

I spent some time making a solution to meet your requirement.

Here's my DB:

Here's my query for whole entity set: (Customers):

http://localhost:5000/odata/customers

Here's my query:

I uploaded a "hard coding" version here for you to reference.

[KenitoInc]

@talalekk Was your issue resolved?