SQL Server: How to remove a key from a Json object

Question

I have a query like (simplified):

SELECT 
    JSON_QUERY(r.SerializedData, '$.Values') AS [Values] 
FROM 
    <TABLE> r 
WHERE ...

The result is like this:

{ "2019":120, "20191":120, "201902":121, "201903":134, "201904":513 }

How can I remove the entries with a key length less then 6.

Result:

   { "201902":121, "201903":134, "201904":513 }

What is your SQL Server version? And you have a JSON obejct, not a JSON array. — Zhorov, Apr 01 '20 at 10:13

Zhorov · Accepted Answer · 2020-04-02T06:06:51.697

One possible solution is to parse the JSON and generate it again using string manipulations for keys with desired length:

Table:

CREATE TABLE Data (SerializedData nvarchar(max))
INSERT INTO Data (SerializedData) 
VALUES (N'{"Values": { "2019":120, "20191":120, "201902":121, "201903":134, "201904":513 }}')

Statement (for SQL Server 2017+):

UPDATE Data
SET SerializedData = JSON_MODIFY(
   SerializedData,
   '$.Values',
   JSON_QUERY(
      (
      SELECT CONCAT('{', STRING_AGG(CONCAT('"', [key] ,'":', [value]), ','), '}')
      FROM OPENJSON(SerializedData, '$.Values') j
      WHERE LEN([key]) >= 6
      )
   )
)

SELECT JSON_QUERY(d.SerializedData, '$.Values') AS [Values]
FROM Data d

Result:

Values
{"201902":121,"201903":134,"201904":513}

Notes:

It's important to note, that JSON_MODIFY() in lax mode deletes the specified key if the new value is NULL and the path points to a JSON object. But, in this specific case (JSON object with variable key names), I prefer the above solution.

SQL Server: How to remove a key from a Json object

1 Answers1