1

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 |
+------+
winner_joiner
  • 12,173
  • 4
  • 36
  • 61
  • 3
    You're almost there -- it should be `with(data nvarchar(255) '$')`, as you want the entire object as the data (the individual strings don't have properties named `d`). – Jeroen Mostert Sep 17 '19 at 12:30
  • 1
    Why not just `select [value] AS Data from `select [value] AS Data from OPENJSON('{"d":["test0","test1","test2"]}', '$.d')` ? – Zhorov Sep 17 '19 at 12:51
  • @JeroenMostert Thanks works, I had just found it. Thanks anyway. Please post it as answer, so that you get the _accepted Answer_. – winner_joiner Sep 17 '19 at 12:51
  • @Zhorov is also an alternative, I didn't think on that. Nice workaround, but I have the `with`solution now. Thank – winner_joiner Sep 17 '19 at 12:55

1 Answers1

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

enter image description here

winner_joiner
  • 12,173
  • 4
  • 36
  • 61
hardikpatel
  • 300
  • 1
  • 5