Atualize uma coluna json após usar JSON_MERGEPATCH

Question

Tendo isso

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"
    }
  ]
}'
);

E o novo 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"
    }
  ]
}

Depois desse POST a resposta me ajudou muito. Mas agora é a hora de atualizar a coluna department_data com o novo json. estou usando esta consulta:

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;

Mas eu tenho esse erro e não sei onde está errado

Erro:

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:

O que está errado, estou seguindo esta etapa: LINK

NOTA: esta é a aparência da minha mesa:

ATUALIZAR

Após aplicar a sugestão de MP0, é assim que minha consulta se parece (duas opções)

Mas o problema é que tenho este erro:

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

Answer 1

Você pode usar:

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;

Saídas:

DEPARTMENT_ID | DEPARTMENT_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                        
------------: | : ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----
          110 {"departamento": "Contabilidade", "funcionários": [{"nome": "Higgins, Shelley", "trabalho": "Gerente de contabilidade", "data de contratação": "2002-06-07T00: 00: 00"} , {"nome": "Gietz, William", "trabalho": "Contador público", "data de contratação": "2002-06-07T00: 00: 00"}, {"nome": "Chen, John", " job ":" Accountant "," rentalDate ":" 2005-09-28T00: 00: 00 "}, {" name ":" Greenberg, Nancy "," job ":" Finance Manager "," HireDate ":" 2002 -08-17T00: 00: 00 "}, {" name ":" Urman, Jose Manuel "," job ":" Contador "," HireDate ":" 2006-03-07T00: 00: 00 "}]}

db <> fiddle aqui

---

Atualizar

Existem algumas coisas erradas com o seu código:

• As JSON_MERGEPATCHnecessidades de envolver a WITH ... SELECTinstrução, pois a saída dessa instrução deve ser o segundo argumento de JSON_MERGEPATCH; e
• Seu JSON é inválido porque está faltando todas as aspas duplas em torno dos identificadores e das strings.

Se você corrigir isso, seu código também funcionará:

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 aqui