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?

asked 05 May '20, 03:54

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%


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

permanent link

answered 05 May '20, 04:41

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 05 May '20, 04:41

1

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!

(05 May '20, 06:52) Baron
Replies hidden

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

(06 May '20, 03:47) Baron

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;

(06 May '20, 08:16) Baron

But at the end it worked! I only needed to add parentheses.

begin

declare json_data long varchar;

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

call sp_parse_json ('sql_array', json_data);

select (sql_array.Product_Colors).color;

end

permanent link

answered 06 May '20, 18:37

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

edited 09 May '20, 07:21

Try this example.

CREATE OR REPLACE VARIABLE arrayvar ARRAY OF ROW(
        "id" int,
        "product_name" varchar(32),
        "product_colors" ARRAY OF ROW( color varchar(12), bbackground varchar(12) )
);

CALL sp_parse_json( 'arrayvar', 
'[{"id":10, "Product_Name":"testname", "Product_Colors":{"color":"white", "background":"black"}},
  {"id":11, "Product_Name":"testname2", "Product_Colors":{"color":"blue", "background":"pink"}}]'
);

SELECT arrayvar[[x.row_num]].id AS id, 
    arrayvar[[x.row_num]].product_name AS product_name,
    arrayvar[[x.row_num]].product_colors.color AS color, 
    arrayvar[[x.row_num]].product_colors.background AS background
    FROM sa_rowgenerator(1,CARDINALITY(arrayvar)) AS x;
permanent link

answered 06 May '20, 11:14

JBSchueler's gravatar image

JBSchueler
3.3k41564
accept rate: 19%

edited 06 May '20, 11:15

1

Jack, I'm by no means a JSON expert at all - but is "product_colors" here not merely of type ROW instead of "ARRAY OF ROW"?

(06 May '20, 12:23) Volker Barth
1

Yes, you are correct, and probably as much or more an expert on JSON as I am. I was adapting another example and I could have / should have simplified this.

As Volker wisely suggests, this works too: "product_colors" ROW( color varchar(12), bbackground varchar(12) )

(06 May '20, 16:01) JBSchueler
Replies hidden

My last code snipped was not complete!

I write once more a working code block example here:

begin

declare json_data long varchar;

declare ss row ("color" varchar(10), "background" varchar(10));

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

call sp_parse_json ('sql_array', json_data);

select sql_array.Product_Colors into ss;

select ss.color;

end

But I wonder why this one does not work (despite it looks logically the same):

begin

declare json_data long varchar;

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

call sp_parse_json ('sql_array', json_data);

select sql_array.Product_Colors.color;

end

(06 May '20, 17:45) Baron
1

Congrats. I was about to say "add parentheses to force the order of precedence" but I see that you beat me to it. Converted your comment to an answer.

(06 May '20, 21:17) JBSchueler

BTW, your example is still not working on my machine! Even after your last change (and even after correcting the typing error background instead bbackground)!

Could you please try it on your machine?

I am eager to know the reason, because I can't find any error!!

(07 May '20, 03:03) Baron

Well, it worked in my case with 17.0.10 latest EBF both the original and the simplified version (the latter with correction for bbackground, as you have also noticed).

(09 May '20, 14:20) Volker Barth
showing 4 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×30
×22

question asked: 05 May '20, 03:54

question was seen: 1,540 times

last updated: 09 May '20, 14:20