SQL Anywhere FOR JSON EXPLICIT how to make an encapsulated array

[cigaras]

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
        }
    ]
}

[Former Member]

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

[fvestjens]

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
    }]
}]