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

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
40.1k361549819
accept rate: 34%

edited 05 Oct '20, 09:26

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

×30

question asked: 15 Sep '20, 03:22

question was seen: 2,016 times

last updated: 22 Jan, 11:15