Update a json column after use JSON_MERGEPATCH

Question

Having this

create table departments_json (
  department_id
    integer
    NOT NULL
    CONSTRAINT departments_json__id__pk PRIMARY KEY,
  department_data
    CLOB
    NOT NULL
    CONSTRAINT departments_json__data__chk CHECK ( department_data IS JSON )
);

insert into departments_json 
json values ( 110, '{
  "department": "Accounting",
  "employees": [
    {
      "name": "Higgins, Shelley",
      "job": "Accounting Manager",
      "hireDate": "2002-06-07T00:00:00"
    },
    {
      "name": "Gietz, William",
      "job": "Public Accountant",
      "hireDate": "2002-06-07T00:00:00"
    }
  ]
}'
);

And the new json :

{
  "employees": [
    {
      "name": "Chen, John",
      "job": "Accountant",
      "hireDate": "2005-09-28T00:00:00"
    },
    {
      "name": "Greenberg, Nancy",
      "job": "Finance Manager",
      "hireDate": "2002-08-17T00:00:00"
    },
    {
      "name": "Urman, Jose Manuel",
      "job": "Accountant",
      "hireDate": "2006-03-07T00:00:00"
    }
  ]
}

After this POST the response help me a lot. But now is time to update the column department_data with the new json. i'm using this query:

update departments_json d
set d.department_data = 
    WITH employees ( json ) AS (
      SELECT j.json
      FROM   departments_json d
             CROSS APPLY JSON_TABLE(
               d.department_data,
               '$.employees[*]'
               COLUMNS (
                 json CLOB FORMAT JSON PATH '$'
               )
             ) j
      WHERE  d.department_id = 110
    UNION ALL
      SELECT j.json
      FROM   JSON_TABLE(
               '{
      employees: [
        {
          name: Chen, John,
          job: Accountant,
          hireDate: 2005-09-28T00:00:00
        },
        {
          name: Greenberg, Nancy,
          job: Finance Manager,
          hireDate: 2002-08-17T00:00:00
        },
        {
          name: Urman, Jose Manuel,
          job: Accountant,
          hireDate: 2006-03-07T00:00:00
        }
      ]
    }',
               '$.employees[*]'
               COLUMNS (
                 json CLOB FORMAT JSON  PATH '$'
               )
             ) j
    )JSON_MERGEPATCH(
         d.department_data,
         (
           SELECT JSON_OBJECT(
                    KEY 'employees'
                    VALUE JSON_ARRAYAGG( json FORMAT JSON RETURNING CLOB )
                    FORMAT JSON
                  )
           FROM   employees
         )
       )
WHERE  d.department_id = 110;

But i got this error, and i don' know where is wrong

Error :

Error en la línea de comandos : 3 Columna : 5
Informe de error -
Error SQL: ORA-00936: falta una expresión
00936. 00000 -  "missing expression"
*Cause:    
*Action:

What is wrong, i'm following this step: LINK

NOTE: this is how my table looks like:

UPDATE

After apply MP0 suggest, this is how my query looks like (two options)

But the problem is that i have this error:

ORA-40478: output value too large (maximum: 4000)

Answer 1

You can use:

UPDATE departments_json
SET department_data = JSON_MERGEPATCH(
         department_data,
         (
           SELECT JSON_OBJECT(
                    KEY 'employees'
                    VALUE JSON_ARRAYAGG( json FORMAT JSON RETURNING CLOB )
                    FORMAT JSON RETURNING CLOB
                  )
           FROM   (
  SELECT j.json
  FROM   departments_json d
         CROSS APPLY JSON_TABLE(
           d.department_data,
           '$.employees[*]'
           COLUMNS (
             json CLOB FORMAT JSON PATH '$'
           )
         ) j
  WHERE  d.department_id = 110
UNION ALL
  SELECT j.json
  FROM   JSON_TABLE(
           '{
  "employees": [
    {
      "name": "Chen, John",
      "job": "Accountant",
      "hireDate": "2005-09-28T00:00:00"
    },
    {
      "name": "Greenberg, Nancy",
      "job": "Finance Manager",
      "hireDate": "2002-08-17T00:00:00"
    },
    {
      "name": "Urman, Jose Manuel",
      "job": "Accountant",
      "hireDate": "2006-03-07T00:00:00"
    }
  ]
}',
           '$.employees[*]'
           COLUMNS (
             json CLOB FORMAT JSON  PATH '$'
           )
         ) j
           )
         )
         RETURNING CLOB
       )
WHERE  department_id = 110;

Outputs:

DEPARTMENT_ID | DEPARTMENT_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                        
------------: | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          110 | {"department":"Accounting","employees":[{"name":"Higgins, Shelley","job":"Accounting Manager","hireDate":"2002-06-07T00:00:00"},{"name":"Gietz, William","job":"Public Accountant","hireDate":"2002-06-07T00:00:00"},{"name":"Chen, John","job":"Accountant","hireDate":"2005-09-28T00:00:00"},{"name":"Greenberg, Nancy","job":"Finance Manager","hireDate":"2002-08-17T00:00:00"},{"name":"Urman, Jose Manuel","job":"Accountant","hireDate":"2006-03-07T00:00:00"}]}

db<>fiddle here

---

Update

There are a couple of things wrong with your code:

• The JSON_MERGEPATCH needs to wrap around the WITH ... SELECT statement as the output from that statement should be the second argument of JSON_MERGEPATCH; and
• Your JSON is invalid as it is missing all the double quotes around the identifiers and the strings.

If you fix that then your code would also work:

update departments_json d
set d.department_data = JSON_MERGEPATCH(
  d.department_data,
  ( -- Start of second argument of JSON_MERGEPATCH
    WITH employees ( json ) AS (
      SELECT j.json
      FROM   departments_json d
             CROSS APPLY JSON_TABLE(
               d.department_data,
               '$.employees[*]'
               COLUMNS (
                 json CLOB FORMAT JSON PATH '$'
               )
             ) j
      WHERE  d.department_id = 110
    UNION ALL
      SELECT j.json
      FROM   JSON_TABLE(
               '{
      "employees": [
        {
          "name": "Chen, John",
          "job": "Accountant",
          "hireDate": "2005-09-28T00:00:00"
        },
        {
          "name": "Greenberg, Nancy",
          "job": "Finance Manager",
          "hireDate": "2002-08-17T00:00:00"
        },
        {
          "name": "Urman, Jose Manuel",
          "job": "Accountant",
          "hireDate": "2006-03-07T00:00:00"
        }
      ]
    }',
               '$.employees[*]'
               COLUMNS (
                 json CLOB FORMAT JSON  PATH '$'
               )
             ) j
    )
    SELECT JSON_OBJECT(
             KEY 'employees'
             VALUE JSON_ARRAYAGG( json FORMAT JSON RETURNING CLOB )
             FORMAT JSON RETURNING CLOB
           )
    FROM   employees
  ) -- End of second argument of JSON_MERGEPATCH
  RETURNING CLOB
)
WHERE  d.department_id = 110;

db<>fiddle here