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

Question

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

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
– Jeroen Mostert, Commented Sep 17, 2019 at 12:30
Why not just select [value] AS Data from select [value] AS Data from OPENJSON('{"d":["test0","test1","test2"]}', '$.d')` ? — Zhorov
– Zhorov, Commented Sep 17, 2019 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
– winner_joiner, Commented Sep 17, 2019 at 12:51
@Zhorov is also an alternative, I didn't think on that. Nice workaround, but I have the withsolution now. Thank — winner_joiner
– winner_joiner, Commented Sep 17, 2019 at 12:55

winner_joiner · Accepted Answer · 2019-09-20 12:21:53Z

5

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

edited Sep 20, 2019 at 12:21

winner_joiner

15.3k4 gold badges42 silver badges71 bronze badges

answered Sep 17, 2019 at 13:28

hardikpatel

3101 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related