SQL query with JSON array

0

Have you tried that SQL on Webcon database in SQL managment studio, or on another one?

WEBCON is using 120 Compatibility Level for SQL Database according to this: https://community.webcon.com/posts/post/compatibility-level-of-webcon-bps-databases-good-practices/323/3
and the OPENJSON requires it at 130: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16

If it was tested on another database, then this could be the reason.

Hi Maksymilian!

Thanks for the good tipp.

Unfortunately it's the same database and compatibility level is set here to 130.

If I test (in Designer Studio) the following query:

SELECT '[{"id" : 1,"code":"925006300000","name": "Test 1"},{"id" : 1,"code":"925003600000","name": "Test 2"}]' AS DET_ATT1

I get the following result:

[',']

It seems like Webcon is trying to substitute everything within {} to empty values.

Maybe because Variables in Webcon have the same pattern than JSON objects, e.g. {WFD_ID}.

Unfortunately I have no idea of how to fix this.

Hi Nik,

I had the same Problem with {} here
https://community.webcon.com/forum/thread/1478?messageid=1478

I've used the ASCII Character code:
Curly right bracket= Char 125
Curly left bracket = Char 123
-- ,'"'+Char(125)+','+Char(123))
Best regards,
Daniel

Thanks Daniel!

I ended up with storing the JSON array with round brackets and replacing them in my JSON query with curly brackets.

Best regards, Nik