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
1.2k324358
accept rate: 20%

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

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;
(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

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

permanent link

answered 05 Oct '20, 08:53

Volker%20Barth's gravatar image

Volker Barth
39.7k358546815
accept rate: 34%

edited 05 Oct '20, 09:26

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×28

question asked: 15 Sep '20, 03:22

question was seen: 1,694 times

last updated: 05 Oct '20, 09:26