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)

MT0 · Accepted Answer · 2020-10-16 21:43:58Z

3

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

edited Oct 16, 2020 at 21:43

answered Oct 16, 2020 at 12:11

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Julio Over a year ago

Hello again (i update my question), look after adapt this code for my case, i got this error: Informe de error - ORA-40478: output value too large (maximum: 4000). For my case i have a first json that has 1000 employees and the second json has 900 employees more. if i execute UNION ALL i see all my employees (1900), but when i execute with the update part, it doesn't work, what could be wrong?. Regards

MT0 Over a year ago

@Julio A string literal can only contain 4000 characters. If you want to pass more than 4000 characters then you will need to use a CLOB or BLOB (or to split it up into chunks smaller than 4000 characters). It may be that you just need to add RETURNING CLOB to the end of the JSON_OBJECT function call. If it isn't that then somewhere in your code this limit is being reached and you will need to debug your code to work out where it is. You can take the parts (i.e. rest request, the WITH ... SELECT and the UPDATE) and run them individually checking the input values and if any fails.

Julio Over a year ago

Hello, if you see my question , i upload a second image, using both options, with returning option (clob) but the error is the same. I have to store the big json (1900) employees, so what do you suggest? Regards

MT0 Over a year ago

@Julio Try changing the 6th from last line in the first example (or line 7 in the second example) from FORMAT JSON to FORMAT JSON RETURNING CLOB.

Julio Over a year ago

you are rigth, it WORKS !!! Thanks so much for all. :)

Collectives™ on Stack Overflow

Update a json column after use JSON_MERGEPATCH

1 Answer 1

Update

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related