How to represent a filter in JSON?

Question

I am writing a frontend application that will provide a UI enabling users to make certain filter selections. We are creating a data visualization that will be populated by data objects fitting the user's filter criteria. For example, user might say:

Show me all data objects that have the emails [email protected], [email protected], or [email protected], but only if those objects also have tags A and B associated with them.

We could represent this in SQL:

SELECT * from dataPoints
WHERE userEmail in ("[email protected]", "[email protected]", "[email protected]")
...

I'm wondering how to best codify this in JSON so that a frontend user can save a filter. We want to store "JSON that represents the user's selections" in a database, retrieve the filter JSON from the database, and apply it to JSON data (i.e. filtering happens in the browser).

I think I can represent query instructions in JSON, something like how it's done here, but I'm wondering... is this not a solved problem? Are there approaches for codifying user filter selections in JSON and for performing client-side filtering?

And to be clear: I'm less concerned with how to apply filter logic to JSON data, and more about "how to represent a user's query preferences in a JSON object." Once I decide how to represent the filter, I can translate that into functions that iterate over JSON data. The other suggested answers are more about how to query JSON. I need to store the query instructions in JSON.

Answer 1

you can use JSON to send array and inner join on it to filter. in example i created some data because you did not provided any.

DECLARE @YOURTABLE TABLE (id int not null identity(1,1), email varchar(50), username varchar(50))
insert into @YOURTABLE values ('[email protected]', 'Aaaaa'),('[email protected]', 'XXXxxxxxx'),('[email protected]', 'Yyyyyyy'),('[email protected]', 'Zzzzz')

--jason can be saved in client or in DB for user.. etc...
DECLARE @paramJSON nvarchar(max) = N'{"emails":["[email protected]","[email protected]","[email protected]"]}'

--this would be inside a stored proc or script
;with _values as (
SELECT email = b.value 
FROM OPENJSON(@paramJSON, '$') 
WITH (
    em NVARCHAR(MAX) '$.emails' AS JSON
) a
CROSS APPLY OPENJSON(a.em,'$') b
)
select v.email, t.id, t.username
from @YOURTABLE t 
INNER JOIN _values v on v.email = t.email

output: as you see, A@... did not show up , since its not matching

email	id	username
[email protected]	2	XXXxxxxxx
[email protected]	3	Yyyyyyy
[email protected]	4	Zzzzz

Answer 2

I like alasql for things like this. If we defined the JSON model, and then think about a helper function to create the JSON filter stricture into a string that the library understands, we can get most of the way there without reinventing the wheel.

{
  "filters": [
    {
      "field": "userEmail",
      "operator": "in",
      "values": ["[email protected]", "[email protected]", "[email protected]"]
    },
    {
      "field": "tags",
      "operator": "containsAll",
      "values": ["A", "B"]
    }
  ],
  "logicalOperator": "AND" // (Or "OR" if needed)
}

Taking that JSON model into the JAvascript example.

import alasql from 'alasql';

const jsonData = [...]; // Your array of data objects
const filterConfig = { /* Your filter JSON from step 1 */ };

const filteredData = alasql(
    'SELECT * FROM ? WHERE ' + buildFilterQuery(filterConfig),
    [jsonData] 
); 

You probably want to think about the filters getting stored, but first pass could be manual or defined when needed. Definitely check out the alasql community though, as it's likely to save you a lot of effort.