I have this sample query: SELECT 1 AS tag, null AS parent, row_num AS [enc_obj!1!row_num] FROM sa_rowgenerator(1, 2) ORDER BY 3 FOR JSON EXPLICIT; I get this as a result: [ { "enc_obj": [ { "row_num": 1 } ] }, { "enc_obj": [ { "row_num": 2 } ] } ] How do I modify the query to get this: { "enc_obj": [ { "row_num": 1 }, { "row_num": 2 } ] } |
This example will do nearly what you want. It just adds a null value. Maybe somebody can help out on that. SELECT 1 AS tag, null AS parent, null AS [enc_obj!1!row_num] FROM sa_rowgenerator(1, 2) UNION select 1 ,1 ,row_num FROM sa_rowgenerator(1, 2) ORDER BY 3, 1 FOR JSON EXPLICIT; The result is: [{ "enc_obj": [{ "row_num": null }, { "row_num": 1 }, { "row_num": 2 }] }] |
not sure if this might help. did a bit of string manipulation tough create or replace function tt() returns long varchar begin declare ls_json long varchar; select ( SELECT 1 AS tag, null AS parent, null as [!1!], null AS [enc_obj!2!row_num] FROM sa_rowgenerator(1, 2) UNION all SELECT 2, 1, null, row_num FROM sa_rowgenerator(1, 2) for json explicit ) into ls_json; set ls_json = substr(ls_json, locate(ls_json,'null,') + 5); set ls_json = left(ls_json,len(ls_json) - 2 ); return ls_json; end select tt() |