Question 366217 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to represent a filter in JSON?

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search