openjson operator

[#1]

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?

[jack_schueler]

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;

[VolkerBarth]

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"?

[jack_schueler]

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

[Baron]

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

[jack_schueler]

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.

[Baron]

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

[VolkerBarth]

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