CODEWITHSUNDEEP
sql-server-2019
Md. Shohag Mia
Md. Shohag Mia

Reputation: 374

How I ignore json values while selecting from table?

My Comments table has a column replies ( type- nvarchar(max)) where I've putted all the replies. It is a array of json, like

[
{
  "Name":{
     "First":"Peter",
     "Last":"Vogel"
  },
  "Text":"Hi",
  "Date":"2017-09-30"
},
{
  "Name":{
     "First":"Jan",
     "Last":"Irvine"
  },
  "Text":"Hello",
  "Date":"2015-01-14"
}
]

I don't want to get the date while selecting from Comments. How can I do that?

I've tried a solution like,

 select  new_replies = JSON_MODIFY([replies], '$[0].date', null)

But this set NULL in date field for only the first reply, not for all.

Upvotes: 0

Views: 404

Answers (1)

Zhorov
Zhorov

Reputation: 29983

JSON_MODIFY() expects the index of an array item to modify it's content and I don't tnink that you can use a wildcard in this situation. But an approach, based on a combination of OPENJSON() and string aggregtaion is an option:

Table:

SELECT *
INTO Comments
FROM (VALUES (N'[
{
"Name":{
    "First": "Peter", 
    "Last": "Vogel"
}, 
 "text": "Hi", 
 "date": "017-09-30"
},
{
"Name":{
    "First": "Jan", 
    "Last": "Irvine"
}, 
 "text": "Hello", 
 "date":"2015-01-14"
}
]')
) v (Replies)

Statement (for SQL Server 2017+):

SELECT NewReplies = (
   SELECT CONCAT('[', STRING_AGG(JSON_MODIFY([value], '$.date', NULL), ','), ']')
   FROM OPENJSON(Replies)
)   
FROM Comments

Statement (for SQL Server 2016):

SELECT NewReplies = CONCAT(
   '[',
   STUFF(
      (
      SELECT ',' + JSON_MODIFY([value], '$.date', NULL) 
      FROM OPENJSON(Replies)
      FOR XML PATH(''), TYPE
      ).value('.', 'nvarchar(max)'), 1, 1, ''
   ),
   ']'
)   
FROM Comments

Result:

NewReplies
-----------------------
[{
"Name":{
    "First": "Peter", 
    "Last": "Vogel"
}, 
 "text": "Hi"
},{
"Name":{
    "First": "Jan", 
    "Last": "Irvine"
}, 
 "text": "Hello"
}]

Upvotes: 1

Related Questions