Suggestion: additional functionality sp_parse_json()

[fvestjens]

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.

[VolkerBarth]

Currently sp_ parse_json() can only parse to a long varchar.

IMHO that's not really true, you can certainly provide a fitting composite variable - but alas, it's usually is ignored...

I certainly second your suggestion. There are several questions here dealing with missing features for JSON import.

[VolkerBarth]

I don't know where I can ask for these suggestions

Historically, the added "product-suggestion" tag has been the means to point out such suggestions. However, I don't know how/whether the SQL Anywhere team takes note of these currently... (There used to be wayyyyyy more official feedback, sigh.)

[Breck_Carter]

> I don't know where I can ask for these suggestions, so maybe someone can help me out on this.

AFAIK this is the only place.