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}
           ]
}

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.