How to construct dynamic SQL where condition against JSON column

Question

I have a SQL table that stores data in Json format. I am using sample data below to understand the issue. Each document type has its own JSON structure.

DocumentID  DocumentTypeID  Status    JsonData  
----------------------------------------------------------------------------  
1             2             Active    {"FirstName":"Foo","LastName":"Bar","States":"[OK]"}  
2             2             Active    {"FirstName":"James","LastName":"Smith","States":"[TX,NY]"}
3             3             Active    {"Make":"Ford","Model":"Focus","Year":"[2020]"}  
4             3             Active    {"Make":"Tesla","Model":"X","Year":"[2012,2015,2019]"}  

then I have another JSON that needs to use in Where condition

@Condition =   '{"FirstName": "James",LastName:"Smith","States":[TX]}'

I will also have DocumentTypeID as parameter

So in normal sql if i hard-code the property names then SQL would look something like

 SELECT * FROM Documents d
 WHERE 
 d.DocumentTypeID = @DocumentTypeID AND
 JSON_VALUE(d.JsonData,'$.FirstName') = JSON_VALUE(@Condition,'$.FirstName') AND
 JSON_VALUE(d.JsonData,'$.LastName') = JSON_VALUE(@Condition,'$.LastName') AND
 JSON_QUERY(d.JsonData,'$.States') = JSON_QUERY(@Condition,'$.States') -- This line is wrong. I have 
                                                                       -- to check if one array is  
                                                                       -- subset of another array

Given
The property names in JsonData column and Condition will exactly match for a given DocumentTypeID.

I already have another SQL table that stores DocumentType and its Properties. If it helps, I can store json path for each property that can be used in above query to dynamically construct where condition

DocumentTypeID      PropertyName             JsonPath         DataType
---------------------------------------------------------------------------------
2                    FirstName                $.FirstName       String
2                    LastName                 $.LastName        String
2                    States                   $.States          Array
3                    Make                     $.Make            String
3                    Model                    $.Model           String
3                    Year                     $.Year            Array

ISSUE
For each document type the @condition will have different JSON structure. How do i construct dynamic where condition? Is this even possible in SQL?

I am using C#.NET so i was thinking of constructing SQL query in C# and just execute SQL Query. But before i go that route i want to check if its possible to do this in TSQL

Answer 1

Unfortunately, JSON support was only added to SQL Server in 2016 version, and still have room for improvement. Working with JSON data that contains arrays is quite cumbersome, involving OPENJSON to get the data, and another OPENJSON to get the array data.
An SQL based solution to this is possible - but a I wrote - cumbersome.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @Documents AS TABLE (
    [DocumentID] int, 
    [DocumentTypeID] int, 
    [Status] varchar(6), 
    [JsonData] varchar(100)
);

INSERT INTO @Documents ([DocumentID], [DocumentTypeID], [Status], [JsonData]) VALUES
(1, 2, 'Active', '{"FirstName":"Foo","LastName":"Bar","States":["OK"]}'),
(2, 2, 'Active', '{"FirstName":"James","LastName":"Smith","States":["TX","NY"]}'),
(2, 2, 'Active', '{"FirstName":"James","LastName":"Smith","States":["OK", "NY"]}'),
(2, 2, 'Active', '{"FirstName":"James","LastName":"Smith","States":["OH", "OK"]}'),
(3, 3, 'Active', '{"Make":"Ford","Model":"Focus","Year":[2020]}'),
(4, 3, 'Active', '{"Make":"Tesla","Model":"X","Year":[2012,2015,2019]}');

Note I've added a couple of rows to the sample data, to verify the condition is working properly.
Also, as a side Note - some of the JSON data in the question was improperly formatted - I've had to fix that.

Then, declare the search parameters (Note: I still think sending a JSON string as a search condition is potentially risky):

DECLARE @DocumentTypeID int = 2,
        @Condition varchar(100) = '{"FirstName": "James","LastName":"Smith","States":["TX", "OH"]}';

(Note: I've added another state - again to make sure the condition works as it should.)

Then, I've used a common table expression with openjson and cross apply to convert the json condition to tabular data, and joined that cte to the table:

WITH CTE AS
(
SELECT FirstName, LastName, [State]
FROM OPENJSON(@Condition)
    WITH (
        FirstName varchar(10) '$.FirstName',
        LastName varchar(10) '$.LastName',
        States nvarchar(max) '$.States' AS JSON
    ) 
    CROSS APPLY OPENJSON(States)
    WITH (
        [State] varchar(2) '$'
    )
)

SELECT [DocumentID], [DocumentTypeID], [Status], [JsonData]
FROM @Documents 
CROSS APPLY 
    OPENJSON([JsonData])
    WITH(
        -- Since we already have to use OPENJSON, no point of also using JSON_VALUE...
        FirstName varchar(10) '$.FirstName',
        LastName varchar(10) '$.LastName',
        States nvarchar(max) '$.States' AS JSON
    ) As JD
    CROSS APPLY OPENJSON(States)
    WITH(
        [State] varchar(2) '$'
    ) As JDS
JOIN CTE 
    ON JD.FirstName = CTE.FirstName
    AND JD.LastName = CTE.LastName
    AND JDS.[State] = CTE.[State]
WHERE DocumentTypeID = @DocumentTypeID 

Results:

DocumentID  DocumentTypeID  Status  JsonData
2           2               Active  {"FirstName":"James","LastName":"Smith","States":["TX","NY"]}
2           2               Active  {"FirstName":"James","LastName":"Smith","States":["OH", "OK"]}