Essentially I have a select that takes data from tables and builds a json:

SELECT JSON_OBJECT(KEY 'data' VALUE
                   JSON_ARRAYAGG(JSON_object(KEY 'code'     VALUE tt.code,
                                             KEY 'name'     VALUE tt.name,
                                             KEY 'location' VALUE
                                             tt.location NULL ON NULL)
                                 RETURNING CLOB),
                   KEY 'header' VALUE
                   JSON_OBJECT(KEY 'vr_code' VALUE 'team1',
                               KEY 'date'    VALUE
                               TO_DATE('2022-12-31', 'YYYY-MM-DD') RETURNING CLOB)
                   RETURNING CLOB) l_json_data
  FROM thetable tt

Currently what I get is this structure, in which each group is returned in individual rows, how can I "concat" them so they would be filled into one "data" array object? Current JSON structure:

{
  "data": [
    {
      "code": "60",
      "name": "michael",
      "location": "canada"
    },
    {
      "code": "60",
      "name": "ken",
      "location": "united states"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
};
{
  "data": [
    {
      "code": "70",
      "name": "jim",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "leslie",
      "location": "mexico"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
}

The desired JSON structure:

{
  "data": [
    {
      "code": "60",
      "name": "michael",
      "location": "canada"
    },
    {
      "code": "60",
      "name": "ken",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "jim",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "leslie",
      "location": "mexico"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
}

CodePudding user response：

Since your query returns multiple rows, your current query would be something like:

SELECT JSON_OBJECT(
         KEY 'data' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'code' VALUE code,
             KEY 'name' VALUE name,
             KEY 'location' VALUE location NULL ON NULL
           )
           RETURNING CLOB
         ) FORMAT JSON,
         KEY 'header' VALUE JSON_OBJECT(
           KEY 'vr_code' VALUE 'team1',
           KEY 'date' VALUE DATE '2022-12-31'
           RETURNING CLOB
         )
         RETURNING CLOB
       ) AS l_json_data
FROM   thetable
GROUP BY something

Which, for the sample data:

CREATE TABLE thetable(code, name, location, something) AS
SELECT '60', 'michael', 'canada', 1 FROM DUAL UNION ALL
SELECT '70', 'jim', 'united states', 2 FROM DUAL;

Outputs:

You need to change it to return a single row which means you need to remove the GROUP BY clause so that it aggregates over the entire result set:

SELECT JSON_OBJECT(
         KEY 'data' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'code' VALUE code,
             KEY 'name' VALUE name,
             KEY 'location' VALUE location NULL ON NULL
           )
           RETURNING CLOB
         ) FORMAT JSON,
         KEY 'header' VALUE JSON_OBJECT(
           KEY 'vr_code' VALUE 'team1',
           KEY 'date' VALUE DATE '2022-12-31'
           RETURNING CLOB
         )
         RETURNING CLOB
       ) AS l_json_data
FROM   thetable

Which, for the same sample data, outputs:

fiddle