|
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() |
