on 2021 Jun 18 7:25 AM
Currently sp_ parse_json() can only parse to a long varchar. It also has limitations on arrays of objects within the json.
The following json:
{ "OrderId": 1, "Lines": [{"Id":1, "Product": "Milk", "Surcharge" : 1.00}, {"Id":2, "Product": "Water"}, {"Id":3, "Product": "Oil", "Surcharge" : 2.00} ] }can not be handled by the sp_ parse_ json() procedure because the objects in the arrays are not equal.
My suggestion would be to use a variable as parameter that corresponds to the format of the json that's expected.
declare variable order_object ROW( OrderId integer, ARRAY OF ROW( Id integer, Product varchar(64), Surcharge numeric(5,2) ) ); call sp_parse_json(order_object,'{...}')
The parser should then map the json on the passed variable and for every element that could not be found it could set it to NULL.
It would even be better if you could set the defaults for the variable if missing in the json like columns in a table. Then bytes for example could be defaulted to 0. But that's currently not possible in a ROW() data type.
I don't know where I can ask for these suggestions, so maybe someone can help me out on this.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.