SQL Anywhere 17: sp_parse_json how to handle properties that are not passed when empty

[fvestjens]

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?

[Vlad]

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-js...

[fvestjens]

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.

[VolkerBarth]

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;

[VolkerBarth]

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...

[former_SQLA_member1694983]

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.

[VolkerBarth]

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...

[former_SQLA_member1694983]

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.