Filter data based on multiple keys from json array

Question

I have a json array in the following format [{"key1":"abc", "key2": "def", "key3": "ghi"}, {"key1":"abc", "key2": "jkl", "key3": "mno"}, ...]. There's also a table inside the database having four columns: key1, key2, key3 and value. Each of key1, key2 and key3 can hold either * or any other string. What I'm trying to achieve is returning an array of values from the value column when the keys from the database match those in the json array. * should mean any value however an exact match should take precedence over *. Here's an example to clarify things up:

Assume the table contains these rows

1. abc | def | ghi | value1
2. abc | def |  *  | value2
3.  *  | def |  *  | value3
4.  *  |  *  |  *  | value4

If the value being checked is {"key1":"abc", "key2": "def", "key3": "ghi"} then value1 should be returned. If we remove row 1 from the database then value2 should be returned. If row 2 was removed as well, value3 should be returned. If row 3 was removed, value4 should be returned and finally if row 4 was removed null should be returned. I'm looking for an efficient way to solve this assuming that the number of rows in the table are relatively small and those in the json array are much larger

Answer 1

You need to define your problem more precisely : how much is a * match worth compared to an exact match ? For example what is best for you : a line with 10 * matchs but zero exact match or the opposite ? If you don't define the "weight" of an exact match then there are many different solutions to your problem.

Then you can define a score for each line based on the number of exact matchs and "*" matchs (weighted with whatever weight you'll define), and select the lines with the highest scores (for example using a sorting algorithm).