SQL Server extract first array element from JSON

Question

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?

Answer 1

Ok, figured it out:

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

This will return the inner message.

Answer 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')