openjson operator

[Baron]

Is there any equivalent to the OPENXML operator for the case of JSON?

I looked in the documentation for something like OPENJSON but couldn't find!!

Any ideas please?

[VolkerBarth · Accepted solution]

Well, it's not called OPENJSON and it's not an operator but a builtin stored procedure:

sp_parse_json system procedure.

Aside: I still generally prefer the DCX doc format but the description there is somehwat outdated because the parameter desription has been modified for 170.10, cf. maxlen vs. maxdepth...

[Baron · Accepted solution]

Thanks a lot. Interesting was that during calling this procedure a variable var would be automatic created and never implicitly dropped.

It took me a while to figure out why the second call of the procedure (within the same connection) with same var and different "json" did not work!

But now is clear!

[Baron · Accepted solution]

Ah, now I see in the link that the drop statement exists already:

DROP VARIABLE IF EXISTS sql_array;

I was reading from my PDF Document, and it was not up to date, and this was the reason for my wonder

[Baron · Accepted solution]

One more question:

In case of (SQL ROW) how can I return the value of a subelement?

For example I can retrieve the Product_Name using:

declare json_data long varchar;

set json_data = '{"id":10, "Product_Name":"testname", "Product_Colors":{"color":"white", "background":"black"}}';

select sql_array.Product_Name;

But How can I retrieve the background of the product (the following block didn't work)?

declare json_data long varchar;

set json_data = '{"id":10, "Product_Name":"testname", "Product_Colors":{"color":"white", "background":"black"}}';

select sql_array.Product_Colors.background;