Why does sp_parse_json does not respect null in JSON Data

[thomas_duemesnil]

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?

[VolkerBarth · Accepted solution]

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.

[VolkerBarth]

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.

[thomas_duemesnil]

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

[VolkerBarth]

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)