Filtering a Table Based on JSON Params in SQL

Published in

The Startup

6 min readNov 15, 2020

Usage of JSON param based table filter in SQL

Background

In all dynamic web applications, the most frequent thing that the backend does is respond with data from the database. And usually, the data that is presented to the user needs to be filtered in specific ways based on what the user wants to see. So, keeping the filtering logic in JSON params might be an option as this gets rid of all the query building logic from the backend. However, we should keep proper security measures in mind while doing this.

Regardless of your opinion on using JSON params to filter a table directly in the SQL DB engine, I think we can agree that it’s quite interesting to ponder how we could do it.

Requirements

First thing’s first, let’s define what we should be able to do:

Filter any table
Filter based on the key (column name) and its value defined in the JSON parameter
Custom conjunction operators (AND or OR)

Side note: Since the result schema will be completely different based on the table being filtered, we won’t be able to do this with an SQL function even though they have many advantages. I can create a separate example for an SQL function with some constraints later.

Let’s get to it

I’ll walk you through the steps needed to create this procedure. I’ll use syntax from the MSSQL server here but you should be able to easily port it to PostgreSQL or any other dialect of your choice:

Step 1: Get the column list

Since we need to be able to filter any table in the database, we should first get the columns that exist in the supplied table.

SELECT
  COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <Table Name>;

Step 2: Parse the supplied JSON parameter

Extract the keys and values in tabular format from the JSON param string.

SELECT
  [key] AS filter_key,
  [value] AS filter_value
FROM OPENJSON(<JSON Param>);

Step 3 (Optional): Error handling for invalid columns in JSON param

If the supplied JSON has some invalid columns, it’s safer to validate the JSON and raise an exception early with a clear error message.

IF EXISTS(
  SELECT [key] AS [name] FROM OPENJSON(<JSON Param>)
  EXCEPT
  SELECT
    COLUMN_NAME AS [name]
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = <Table Name>
)
-- Handle error

Step 4: Generate and execute SQL statement

Since the table and columns are all dependent on the supplied parameters, we need to generate a SQL statement dynamically to create the actual query that filters the table. This can be achieved with the following query:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + (  --
  -- Append comma separated string of column names in <Table Name>
  --
  SELECT
    STRING_AGG([column_name], ',')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = <Table Name>
)+ N' FROM ' + <Table Name> + ' s
WHERE ' + (  --
  -- Generate and add the where clause based on
  -- <JSON param> and <Custom Conjunction>
  --
  SELECT
    STRING_AGG(
      ' s.' + [key] + ' = ''' + [value] + ''' ',
      <Custom Conjuction>
    )
  FROM OPENJSON(<JSON Param>)
);-- Show the generated SQL query
PRINT @sql;-- Execute the generated SQL query to get the filtered result set
EXEC sp_executesql @sql;

Final Procedure

Now let’s put it all together and… Voila! we have our very own JSON-based filtering SQL procedure.

DROP PROCEDURE IF EXISTS dbo.apply_filter;
GO/*
 * Procedure to filter any table based on JSON params in SQL
 */
CREATE PROCEDURE dbo.apply_filter (
  @table NVARCHAR(255),
  @params NVARCHAR(MAX),
  @conjunction NVARCHAR(3) = 'AND'
)
AS
BEGIN  DECLARE @columns TABLE (
    [name] NVARCHAR(255)
  );  --
  -- Step 1: Get the column list
  --
  INSERT INTO @columns
  SELECT
    column_name
  FROM INFORMATION_SCHEMA.columns
  WHERE TABLE_NAME = @table;  DECLARE @param_table TABLE (
    filter_key VARCHAR(255),
    filter_value NVARCHAR(MAX)
  );  --
  -- Step 2: Parse the supplied JSON parameter
  --
  INSERT INTO @param_table (filter_key, filter_value)
  SELECT
    [key] AS filter_key,
    [value] AS filter_value
  FROM OPENJSON(@params);  DECLARE @invalid_keys NVARCHAR(MAX) = (
    SELECT
      STRING_AGG([name], ', ')
    FROM (
      SELECT [filter_key] AS [name] FROM @param_table
      EXCEPT
      SELECT [name] FROM @columns
    ) x
  );  --
  -- Step 3: Error handling for invalid columns in JSON
  --
  IF (@invalid_keys <> '')
  BEGIN
    DECLARE @e VARCHAR(MAX) = CONCAT(
      'The source table does not have some filter key(s) that exist in the param: ',
      @invalid_keys
    );  THROW 51000, @e, 1;
  END  --
  -- Step 4: Generate dynamic SQL statement
  --
  DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + (
    --
    -- Append comma separated string of column names in <Table Name>
    --
    SELECT STRING_AGG([name], ',') FROM @columns
  )
  + N' FROM ' + @table + ' s
  WHERE ' + (
    --
    -- Generate and add the where clause based on
    -- <JSON param> and <Custom Conjunction>
    --
    SELECT
      STRING_AGG(
        ' s.' + [filter_key] + ' = ''' + [filter_value] + ''' ',
        @conjunction
      )
    FROM @param_table
  );  -- Show the generated SQL query
  PRINT @sql;  -- Execute the generated SQL query to get the filtered result set
  EXEC sp_executesql @sql;
END

Usage and Output

-- Generic usage
EXEC apply_filter '<table name>', 'json param' [, <conjunction>];

Test data

SELECT * FROM students;-- Result set

| id  | name | date_of_birth | grade |
| --- | ---- | ------------- | ----- |
| 1   | abc  | 2020-01-01    | 1     |
| 2   | abc  | 2020-01-01    | 2     |
| 3   | abc  | 2020-01-02    | 1     |
| 4   | abc  | 2020-01-02    | 2     |
| 5   | def  | 2020-01-01    | 1     |
| 6   | def  | 2020-01-01    | 2     |
| 7   | def  | 2020-01-02    | 1     |
| 8   | def  | 2020-01-02    | 2     |

Case 1: With a single filter key in JSON param

EXEC apply_filter 'students', '{"grade":"2"}';-- Result set| id  | name | date_of_birth | grade |
| --- | ---- | ------------- | ----- |
| 2   | abc  | 2020-01-01    | 2     |
| 4   | abc  | 2020-01-02    | 2     |
| 6   | def  | 2020-01-01    | 2     |
| 8   | def  | 2020-01-02    | 2     |-- Generated Query
SELECT id,name,date_of_birth,grade FROM students s
WHERE  s.grade = '2'

Case 2: With multiple filter keys in JSON param

EXEC apply_filter 'students', '{"grade":"2", "name":"abc"}';-- Result set
| id  | name | date_of_birth | grade |
| 2   | abc  | 2020-01-01    | 2     |
| 4   | abc  | 2020-01-02    | 2     |-- Generated query
SELECT id,name,date_of_birth,grade FROM students s
WHERE  s.grade = '2' AND s.name = 'abc'

Case 3: With custom OR conjunction

EXEC apply_filter 'students', '{"grade":"2", "name":"abc"}', 'OR';-- Result set
| id  | name | date_of_birth | grade |
| 1   | abc  | 2020-01-01    | 1     |
| 2   | abc  | 2020-01-01    | 2     |
| 3   | abc  | 2020-01-02    | 1     |
| 4   | abc  | 2020-01-02    | 2     |
| 6   | def  | 2020-01-01    | 2     |
| 8   | def  | 2020-01-02    | 2     |-- Generated Query
SELECT id,name,date_of_birth,grade FROM students s
WHERE  s.grade = '2' OR s.name = 'abc'

Conclusion

As you can see, we can create a dynamic filter on any table based on a JSON parameter with custom conjunction in just 4 simple steps. There are of course a few caveats to this implementation. One that comes to mind is the inability to leverage the custom JSON filtering in a larger query with JOINS or in views. Also, we won’t be able to combine multiple conditional operators (AND and OR) in the current implementation. However, these are problems that can be addressed with a bit more complicated logic.

If you have any ideas that can help improve this implementation then feel free to create an issue or a PR in this repo. Also, let me know if you think I should create a guide for a function version (instead of a procedure) for this filter.