on 2020 Sep 15 4:22 AM
If you have agreed on a json format like for example:
{ "firstName": "Frank","middleName": null,"lastName": "Vestjens"}
Then the json is also valid if the middleName is not mentioned
{ "firstName": "Frank","lastName": "Vestjens"}
But then the sp_parse_json does not know the middleName. Because if I use test.middleName I'll get the error
"Column 'middleName' not found in variable 'test'"
Is there a way to test whether this middleName exists after parsing the json?
So basically I guess you will have to try to SELECT the expected value within exception-handling code: If this returns SQLCODE -1595, you know the value was not provided, and you might SELECT NULL instead, so something like
begin create or replace variable rowvar row( "firstname" nvarchar(255), "middleName" nvarchar(255), "lastName" nvarchar(255)); call sp_parse_json('rowvar', '{ "firstName": "Frank", "lastName": "Vestjens"}'); begin declare EXC_COLUMN_NOT_FOUND_IN_VARIABLE exception for SQLSTATE '52006'; select rowvar.firstName as firstName, rowvar.middleName as middleName, rowvar.lastName as lastName; exception when EXC_COLUMN_NOT_FOUND_IN_VARIABLE then message 'Element "middleName" does not exist.' to client; -- (or any other selected element...!) select rowvar.firstName as firstName, null as middleName, rowvar.lastName as lastName; end; drop variable rowvar; end;
Note, that unless you parse the error message itself, you would need to try to access each row field individually to know which one(s) are not provided via the JSON format.
I think this is something that should be improved with SQL Anywhere's sp_parse_json() implementation...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I don't know but another suggested approach was to use external environments with (better) builtin JSON import and/or conversion from JSON to XML (although in my limited understanding, this isn't necessarily loss-less...)
See here for the discussion...
User | Count |
---|---|
68 | |
8 | |
8 | |
6 | |
6 | |
6 | |
6 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.