Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 15 Sep '20, 03:22

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 21%

(15 Sep '20, 03:52) Vlad

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.

(15 Sep '20, 04:04) Frank Vestjens

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.

        "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'"
   rowvar.firstName as firstName,
   rowvar.middleName as middleName, 
   rowvar.lastName as lastName;
(05 Oct '20, 08:18) Volker Barth

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

   create or replace variable rowvar row(
      "firstname" nvarchar(255),
      "middleName" nvarchar(255),
      "lastName" nvarchar(255));

   call sp_parse_json('rowvar', '{ "firstName": "Frank", "lastName": "Vestjens"}');

      declare EXC_COLUMN_NOT_FOUND_IN_VARIABLE exception for SQLSTATE '52006';
         rowvar.firstName as firstName,
         rowvar.middleName as middleName, 
         rowvar.lastName as lastName;
         message 'Element "middleName" does not exist.' to client; -- (or any other selected element...!)
            rowvar.firstName as firstName,
            null as middleName, 
            rowvar.lastName as lastName;
   drop variable rowvar;

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

permanent link

answered 05 Oct '20, 08:53

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 05 Oct '20, 09:26


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

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Sep '20, 03:22

question was seen: 2,212 times

last updated: 22 Jan, 11:15