Cập nhật cột json sau khi sử dụng JSON_MERGEPATCH

Question

Có cái này

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

Và json mới:

{
  "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"
    }
  ]
}

Sau khi ĐĂNG này phản hồi giúp tôi rất nhiều. Nhưng bây giờ là lúc để cập nhật dữ liệu của cột với json mới. tôi đang sử dụng truy vấn này:

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;

Nhưng tôi gặp lỗi này, và tôi không biết sai ở đâu

Lỗi :

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:

Có gì sai, tôi đang làm theo bước này: LINK

LƯU Ý: đây là cách bảng của tôi trông như thế nào:

CẬP NHẬT

Sau khi áp dụng đề xuất MP0, đây là cách truy vấn của tôi trông như thế nào (hai tùy chọn)

Nhưng vấn đề là tôi gặp lỗi này:

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

Answer 1

Bạn có thể dùng:

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;

Kết quả đầu ra:

DEPARTMENT_ID | DEPARTMENT_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                        
------------: | : ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----
          110 | {"Department": "Kế toán", "nhân viên": [{"name": "Higgins, Shelley", "job": "Accounting Manager", "RentDate": "2002-06-07T00: 00: 00"} , {"name": "Gietz, William", "job": "Public Accountant", "RentDate": "2002-06-07T00: 00: 00"}, {"name": "Chen, John", " job ":" Accountant "," RentDate ":" 2005-09-28T00: 00: 00 "}, {" name ":" Greenberg, Nancy "," job ":" Finance Manager "," RentDate ":" 2002 -08-17T00: 00: 00 "}, {" name ":" Urman, Jose Manuel "," job ":" Accountant "," RentDate ":" 2006-03-07T00: 00: 00 "}]}

db <> fiddle here

---

Cập nhật

Có một số điều sai với mã của bạn:

• Các JSON_MERGEPATCHnhu cầu bao quanh WITH ... SELECTcâu lệnh vì đầu ra từ câu lệnh đó phải là đối số thứ hai của JSON_MERGEPATCH; và
• JSON của bạn không hợp lệ vì nó thiếu tất cả các dấu ngoặc kép xung quanh số nhận dạng và chuỗi.

Nếu bạn khắc phục được điều đó thì mã của bạn cũng sẽ hoạt động:

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