sql - Return string in a array without object

Question

This is how I am getting:

 "skills":[
                  {
                     "cityname":"Kaduna North"
                  }
               ]

How I want is:

"skills":["Kaduna North"]

is that possible?

标签： sqljsonsql-server

Answer 1

试试下面的：

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{
            "skills": [{
                        "cityname": "Kaduna North"
                       }]
            }'

;

SELECT 
     concat('"skills":["',
            JSON_VALUE(@jsonInfo,'$.skills[0].cityname')
             ,'"]'
     )
;

或使用以下内容：

 SELECT
    JSON_QUERY(
         concat('["',
               JSON_VALUE(@jsonInfo,'$.skills[0].cityname')
               ,'"]'
    ) ) as skills
 FOR JSON PATH