I get a JSON Array from an external System. When no data is available the system insert the string null in the Data. When the Data is parsed with sp_parse_json the returned data contains a 0 for that element instead of a SQL NULL.

BEGIN 
declare cJson varchar(50) default '[null, 0, 1472478300]';
drop variable if exists myarray;
call sp_parse_json('myarray', cJson);
select val from unnest( myarray ) as da("val");
END;

Returns

val
0
0
1472478300

The null exists in the JSON Definition at www.json.org. Do I miss something? Is it planned to follow the JSON definition in the future? Is SA17 behave differently?

asked 07 Sep '16, 06:15

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k263661
accept rate: 17%

edited 07 Sep '16, 07:48

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674


Hm, the special JSON null value seems to be parsed partly wrong, possibly due to the other supplied values:

In my tests with 16.0.0.2270, this is returned:

'[null]' returns (NULL)
'[null, null]' returns (NULL), (NULL)
'[null, 0]' returns 0,0
'[null, "0"]' returns (NULL), '0'
'[null, "foo"]' returns (NULL), 'foo'
'[null, "null"]' returns (NULL), 'null'

In my humble impression, there seems to be a bug that as soon as a number is involved, null is treated as 0.

permanent link

answered 07 Sep '16, 06:45

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

converted 07 Sep '16, 06:46

Good observation Volker. As always. Lets see if some official can clarify if that's a bug or a feature.

(07 Sep '16, 07:22) Thomas Dueme...

FWIW, the behaviour seems similar with 17.0.4.2100.

I noticed a further detail: The null value is treated as expected if the array contains one of the other special values, i.e. true or false, even if a number is involved, too:

'[true, null, 1]' return true, (NULL), 1


The behaviour seems correct when using a JSON object, such as:

begin
   declare cJson varchar(50) default '{foo: "null", bar: 0, val: null}';
   drop variable if exists myrow;
   call sp_parse_json('myrow', cJson);
   select myrow.foo, myrow.bar, myrow.val;
end;
 -- returns 'null', 0, (NULL)

(07 Sep '16, 07:47) Volker Barth
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:

×206
×18
×5

question asked: 07 Sep '16, 06:15

question was seen: 309 times

last updated: 07 Sep '16, 07:48