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 |
+------+