CODEWITHSUNDEEP
sqljsonsql-server
Anil
Anil

Reputation: 1857

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: "[email protected]"}
2       {FirstName:"syx", LastName: "ave", Email: "[email protected]"}

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"}

Upvotes: 1

Views: 2828

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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": "[email protected]"}')
,(2,'{"FirstName":"syx", "LastName": "ave", "Email": "[email protected]"}');

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.

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81990

With corrected JSON, you can use JSON_MODIFY() and set the desired value to NULL

Example

Declare @YourTable Table ([Id] int,[Person] varchar(500))
Insert Into @YourTable Values 
 (1,'{"FirstName":"test", "LastName": "test", "Email": "[email protected]"}')
,(2,'{"FirstName":"syx", "LastName": "ave", "Email": "[email protected]"}')

Select A.ID 
      ,NewValue = JSON_MODIFY([Person],'$.Email',null)
 From  @YourTable A

Returns

ID  NewValue
1   {"FirstName":"test", "LastName": "test"}
2   {"FirstName":"syx", "LastName": "ave"}

Upvotes: 9

Related Questions