SQL Anywhere FOR JSON EXPLICIT how to make an encapsulated array

Question

I have this simple query in SQL Anywhere 17, one column two rows:

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, an array of separate arrays for every row:

[
    {
        "enc_obj": [
            {
                "row_num": 1
            }
        ]
    },
    {
        "enc_obj": [
            {
                "row_num": 2
            }
        ]
    }
]

How do I modify the query to get single array with all rows in it, like this:

{
    "enc_obj": [
        {
            "row_num": 1
        },
        {
            "row_num": 2
        }
    ]
}

Any ideas?

Answer 1

You can simply omit the enc_obj criterium from the json directive:

SELECT
    1       AS tag,
    null    AS parent,
    row_num AS [!1!row_num]    -- NOTE: "enc_obj" is dropped!
FROM
    sa_rowgenerator(1, 2)
ORDER BY
    3
FOR JSON EXPLICIT;

As result you'll get an anonymous JSON array without an encapsulating object:

[
  {
    "row_num": 1
  },
  {
    "row_num": 2
  }
]

Can you work with that?