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.