How to ignore few properties in SQL Server JSON value

I have a table which has a column with JSON data. Every JSON object in each column has many properties. I have another table which has property name not all but few.

What I need to do is to write a query to select JSON data from table but JSON should not contain properties which are there in the second table. For example, below is the jSON table.

Id      Person
1       {FirstName:"test", LastName: "test", Email: "[e-mail]"}
2       {FirstName:"syx", LastName: "ave", Email: "[e-mail]"}

Second table with properties name:

ExclusionId   ExcludedProperty
1              Email

Query should join these two table and output should be

 Id          Person
  1       {FirstName:"test", LastName: "test"}
  2       {FirstName:"syx", LastName: "ave"}

Answer

This is a fully generic approach with no need for dynamic SQL:

--a mockup to simulate your issue
--credits to John Cappelletti for the mockup-code
DECLARE @YourTable TABLE (Id INT,Person NVARCHAR(500))
INSERT INTO @YourTable VALUES 
 (1,'{"FirstName":"test", "LastName": "test", "Email": "[e-mail]"}')
,(2,'{"FirstName":"syx", "LastName": "ave", "Email": "[e-mail]"}');

DECLARE @ExcludeProperties TABLE (Id INT,PropName VARCHAR(500))
INSERT INTO @ExcludeProperties VALUES (1,'Email');

–The query

WITH cte AS
(
    SELECT t.Id
          ,JsonValues.[key] AS PropName
          ,JsonValues.[value] AS PropValue
    FROM @YourTable t
    CROSS APPLY OPENJSON(t.Person) jsonValues
    WHERE JsonValues.[key] COLLATE Latin1_General_CI_AS NOT IN(SELECT excl.PropName COLLATE Latin1_General_CI_AS FROm @ExcludeProperties excl)
)
SELECT cte.Id
      ,CONCAT('{',
      STUFF((
        SELECT CONCAT(',"',cte2.PropName,'":"',cte2.PropValue,'"')
        FROM cte cte2
        WHERE cte2.Id=cte.Id 
        FOR XML PATH('')
      ),1,1,'')
      ,'}')
FROM cte
GROUP BY cte.Id;

The idea in short:

First we use a CTE to create a list of all properties within your JSON as EAV (Entity-Attribute-Value). With this its easy to get rid of all the attributes, where the name is found in your exclude table.

As we are not allowed to use a column’s value as the output column’s alias we can build the JSON with string-methods instead of a FOR JSON query.

First I use a GROUP BY to reduce the final output to one-row-per-Id, and the I use a correlated sub-query to build the JSON per Id from the corresponding EAVs.