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.6k283763
accept rate: 16%

edited 07 Sep '16, 07:48

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716


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
33.8k330486716
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

FWIW, this old one seems to be fixes with 16.0.0.2798 and 17.0.0.4935 - I checked with v16 that null is now always returned as NULL in the samples above. In my understanding, the bug had to do with null being the first element...:

    ================(Build #2766  - Engineering Case #817415)================

    The sp_parse_json function can be extremely slow when there are null values 
    in first set and many sets follow in the JSON input string. An example of 
    this follows:

    [{a:10,b:z1,c:null}, {a:11.2,b:z2,c:301}, ...]

    In this case, the algorithm performance becomes Order N-squared (O(N2)). 
    Instead of returning a result in seconds, it can take several minutes, depending 
    on the number of sets.

    This problem has been fixed.

    Also, an incorrect result is returned for sets where the first value is 
    null and subsequent values are integer, floating-point, or Boolean types. 
    Instead of null, the first result is 0. The following is an example:

    CALL sp_parse_json('tvar', '[{x:null}, {x:1}, {x:2}]');
    SELECT tvar[[1]].x,tvar[[2]].x,tvar[[3]].x;

    This problem has been fixed.

    If the output row/array variable (argument 1) is defined before calling 
    sp_parse_json, the row/array variable is usually rejected and an error is 
    returned. The following is an example:

    CREATE OR REPLACE VARIABLE tvar ARRAY OF ROW( 
            a VARCHAR(32), 
            b ARRAY OF ROW( b1 LONG NVARCHAR, b2 LONG NVARCHAR),
            c BIT,
            d NUMERIC(5,2)
    );

    CALL sp_parse_json('tvar', '[{a:"json", b:[{b1:"hello", 
    b2:"goodbye"},{b1:"say", b2:"again"}], c:true, 
    d:12.34},
    {a:"json2", b:[{b1:"hello2", b2:"goodbye2"},{b1:"say2", 
    b2:"again2"}], c:false, d:56.78}]');

    SELECT tvar[[x.row_num]].a AS a, 
        tvar[[x.row_num]].b[[y.row_num]].b1 AS b1, 
        tvar[[x.row_num]].b[[y.row_num]].b2 AS b2,
        tvar[[x.row_num]].c AS c,
        tvar[[x.row_num]].d AS d
        FROM sa_rowgenerator(1,CARDINALITY(tvar)) AS x, sa_rowgenerator(1,CARDINALITY(tvar[[1]].b)) 
    AS y;

    This problem has been fixed. The sp_parse_json function will now accept 
    a wider variety of predefined output row/array variables.
permanent link

answered 06 Feb, 03:58

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

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:

×243
×20
×8

question asked: 07 Sep '16, 06:15

question was seen: 1,339 times

last updated: 06 Feb, 03:58