DB2 and JSON, inconsistent quotation of integers

44 Views Asked by At

I'm using DB2 for i.

The following SQL uses the JSON_ARRAY function in order to produce a json array of three integers.

VALUES JSON_ARRAY(1,2,3)

Result:

00001
[1,2,3]

This seems just right. But when I put it into a function, something changes.

CREATE FUNCTION MYSCHEMA.CREATEJSONARRAY01()
RETURNS VARCHAR(512)
LANGUAGE SQL
CONTAINS SQL
BEGIN
    RETURN JSON_ARRAY(1,2,3);
END;

VALUES MYSCHEMA.CREATEJSONARRAY01()

Here I get:

00001
["1","2","3"]

That is not quite right.

The two examples should produce the same output and first of all I made some tests to see where the problem originates.

Is it the conversion to text?

VALUES CAST(JSON_ARRAY(1,2,3) AS VARCHAR(512))

This produces the same output of the non-cast version, so the problem does not originate when the entire JSON is converted to string.

I found a solution that is adding FORMAT JSON to each array element.

CREATE FUNCTION MYSCHEMA.CREATEJSONARRAY02()
RETURNS VARCHAR(512)
LANGUAGE SQL
CONTAINS SQL
BEGIN
    RETURN JSON_ARRAY(1,2 FORMAT JSON,3 FORMAT JSON);
END;
00001
["1",2,3]

The reason why it behaves this way however is still unclear to me and that's the main reason of this question.

0

There are 0 best solutions below