sql-serversql-server-2016sql-server-json

How to select string Rows from MS SQL Json String array?


I have a Text String which contains JSON, something like this:

'{ "d" : [ "test0", "test1", "test2" ] }'

and I would like to retrieve the item of the Array as rows.

+------------+
|   data     | 
+------------+ 
|  test0     | 
|  test1     | 
|  test2     | 
+------------+  

all examples on the Web, show how it is done with "Object Array", but I would like to do it with a simple "String Array" MS example.

The default query

select * from OPENJSON('{"d":["test0","test1","test2"]}', '$.d')

just returns a table with the key, value, type of each entry

+-----+-------+------+
| key | value | type |
+-----+-------+------+
|   0 | test0 |    1 |
|   1 | test1 |    1 |
|   2 | test2 |    1 |
+-----+-------+------+

the problem is, I don't know how to set the with part of the query, so that the query returns a row.

select * from OPENJSON('{"d":["test0","test1","test2"]}', '$.d')
with(data nvarchar(255) '$.d')

only return:

+------+
| data |
+------+
| NULL |
| NULL |
| NULL |
+------+

Solution

  • select * from OPENJSON('{"d":["test0","test1","test2"]}', '$.d') 
        with(data nvarchar(255) '$')  
    

    enter image description here