About the multilayer parsing problems encountered when parsing with JSON parameters in SQL Server 2017

Source: Multi-layered parsing problems encountered when parsing with JSON parameters in SQL Server 2017

To develop a new system, the DB section uses the JSON parsing method that SQL Server comes with from version 2016.

Used for almost half a year, in individual projects, as well as part of the company's projects, and for the time being there were no major problems, and performance issues.

Today in the parsing of JSON multilevel parameters encountered a little problem, here to record:

The contents of the communication are as follows:

1 declare @json varchar (max) =n ' {"Customer": $, "positions": [{"ID": 0, "Quantity": 180000, "Files": [ 180,177,175,172,170,169,167,164,161,162,159,158,153,152]}]} ';

Want to parse out the files in positions, but always error, or null.

Query the document and find the type of field that this parsing needs to be made into JSON.

The parsed SQL script is as follows:

1 SelectID, Quantity, FileID=F.value2  fromOpenjson (@json,'$. Positions')3  with (4Idint, 5Quantityint,6Filesnvarchar(Max)'$. Files'  asJSON7 ) x8  CrossApply Openjson (X.files,'$') F

* Note: The 6th line is fixed: The field type and the following.

About the multilayer parsing problems encountered when parsing with JSON parameters in SQL Server 2017