Repeating JSON Aggregated Array with Row Limits

I have an Oracle 19 database with a JSON aggregated array which I need to cut off and repeat after a set number of rows, for example:

[{"personId": "13274614","surname": "SMITH"},{"personId": "13275037","surname": "JONES"}]
[{"personId": "13275038","surname": "THOMAS"},{"personId": "13275039","surname": "BAKER"}]

…etc. Here’s some basic SQL to illustrate how my current dataset is put together:

    SELECT
    json_arrayagg(json_object('personId' VALUE person.id,
                              'surname'  VALUE person.surname
                              )
    RETURNING CLOB) as customer_json
    FROM
    person

I need to push customer data to an API endpoint. Pushing 1 customer record at a time is not performant, but the entire dataset is in the millions so cannot fit in a single array, therefore I need to, if possible, achieve the best of both worlds – 200 customer records in an array, which then ends and the next row starts a fresh array with the next 200 customers.

I have searched and found some mentions of a LIMIT function, but have not been able to successfully apply this.

Does anyone have an idea on how I can achieve this?

Answer

From Oracle 12, you can use ORDER BY ... OFFSET ... FETCH NEXT ... to limit the number of rows inside a sub-query:

SELECT json_arrayagg(
         json_object(
           'personId' VALUE id,
           'surname'  VALUE surname
         )
         RETURNING CLOB
       ) as customer_json
FROM   (
  SELECT id, surname
  FROM   person
  ORDER BY id
  OFFSET 0 ROWS
  FETCH NEXT 200 ROWS ONLY
)

Before Oracle 12, you can use the ROW_NUMBER analytic function:

SELECT json_arrayagg(
         json_object(
           'personId' VALUE id,
           'surname'  VALUE surname
         )
         RETURNING CLOB
       ) as customer_json
FROM   (
  SELECT id,
         surname,
         ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM   person
)
WHERE  rn BETWEEN 1 AND 200;

---

If you want all the rows in 200 row groups then:

SELECT json_arrayagg(
         json_object(
           'personId' VALUE id,
           'surname'  VALUE surname
         )
         RETURNING CLOB
       ) as customer_json
FROM   (
  SELECT id,
         surname,
         CEIL(ROW_NUMBER() OVER (ORDER BY id)/200) AS grp
  FROM   person
)
GROUP BY grp;