|
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... Hello. I'm replying to this messages in hope that a better way has been discovered since. I'm having the exact same issue, but my selection is much larger than Frank's.
(22 Jan, 09:14)
adriancttnc
Replies hidden
1
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...
(22 Jan, 09:42)
Volker Barth
Thanks for the reply. I'll have to do some research on the external procedures. We already manage our JSONs in JavaScript before sending them to the DB, but I was trying to put some safeguards up in case the properties somehow still end up missing.
(22 Jan, 11:15)
adriancttnc
|
Can you please tell the exact version of SA17?
For your reference: https://sqlanywhere-forum.sap.com/questions/28039/why-does-sp_parse_json-does-not-respect-null-in-json-data
The build is 6089.
It has nothing to do with the null reference. That's working fine. It's about not having the property in the json.
FWIW, that does even happen when you declare the row (or array of rows) variable beforehand - which would be appropriate when there is an agreement on the JSON format.
CREATE OR REPLACE VARIABLE rowvar ROW( "firstname" nvarchar(255), "middleName" nvarchar(255), "lastName" nvarchar(255)); CALL sp_parse_json( 'rowvar', '{ "firstName": "Frank", "lastName": "Vestjens"}'); -- return SQLCODE -1595: "Column 'middleName' not found in variable 'rowvar'" SELECT rowvar.firstName as firstName, rowvar.middleName as middleName, rowvar.lastName as lastName; DROP VARIABLE rowvar;