7

I have json stored in one of the columns in SQL Server and I need to modify it to remove the square brackets from it. The format is as below. Can't seem to find a good way of doing it.

[ { "Message":"Info: this is some message here.", "Active":true } ]

One way is to do it using below query, but this query is very very slow and I need to run on a very large set of data.

select a.value
from dbo.testjson e
cross apply OPENJSON(e.jsontext) as a
where isjson(e.jsontext) = 1

The only other way I can think of is just doing string manipulation but it can be error prone. Could someone help with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
huzk
  • 171
  • 1
  • 6

2 Answers2

10

Ok, figured it out:

select 
    json_query(
        '[{"Message":"Info: this is some message here.","Active":true}]',
        '$[0]'
    )

This will return the inner message.

zcoop98
  • 2,590
  • 1
  • 18
  • 31
huzk
  • 171
  • 1
  • 6
  • 1
    I was searching for ages before I found this gem '$[0]' to work with the first element of a JSON array. – Stu Price Nov 24 '21 at 11:50
2

You should add the property name, in this case Message, in order to get only that part. Keep in mind that it's case sensitive. Something like;

 select json_value('[{"Message":"Info: this is some message here.","Active":true}]', '$[0].Message')
Geraldo Diaz
  • 346
  • 5
  • 7