How to add a new JSON array field to existing MySQL JSON array

Question

I have a MySQL (v5.7) database that has a table called deals which contains a JSON column called jdeal_status.

This column contains data in the following format:

{
   "trackIssues":[
      {
         "date":[
            "2020-01-28T12:04:42-05:00"
         ],
         "notes":[
            {
               "date":"2020-01-28T12:30:46-05:00",
               "name":"Joe Robbs",
               "note":"New note"
            },
            {
               "date":"2020-01-28T17:45:29-05:00",
               "name":"John Ha",
               "note":"New attachment"
            }
         ],
         "value":3000,
         "isResolved":false,
         "issueIndex":0
      },
      { ...other array objects },
   ]
 }

I would like to add a new array field called attachments to the trackIssues array (at trackIssues[0]) if it doesn't exist or append to it if it does exist. However, I am stuck on just adding the new field to the existing array.

I have tried doing the following:

UPDATE deals set jdeal_status = JSON_ARRAY_APPEND(jdeal_status, '$.trackIssues[0]', 
JSON_OBJECT(
    'attachments', 
    JSON_OBJECT(
      'date', "2020-01-28T12:04:42-05:00",
      'file', 'yyyy.pdf'
    ) 
))  WHERE id = 2;"

but it appends to the trackIssues array and not element at [0] as follows:

{
   "trackIssues":[
      [
         {
            "date":[
               "2020-01-28T12:04:42-05:00"
            ],
            "notes":[
               {
                  "date":"2020-01-28T12:30:46-05:00",
                  "name":"Joe Robbs",
                  "note":"New note"
               },
               {
                  "date":"2020-01-28T17:45:29-05:00",
                  "name":"John Ha",
                  "note":"New attachment"
               }
            ],
            "value":3000,
            "isResolved":false,
            "issueIndex":0
         },
         {
            "attachments":{
               "date":"2020-01-28T12:04:42-05:00",
               "file":"yyyy.pdf"
            }
         }
      ]
   ]
 }

Whereas I want this:

   "trackIssues":[
      {
         "date":[
            "2020-01-28T12:04:42-05:00"
         ],
         "notes":[
            {
               "date":"2020-01-28T12:30:46-05:00",
               "name":"Joe Robbs",
               "note":"New note"
            },
            {
               "date":"2020-01-28T17:45:29-05:00",
               "name":"John Ha",
               "note":"New attachment"
            }
         ],
         "value":3000,
         "isResolved":false,
         "issueIndex":0,
         "attachments":[
            {
               "date":"2020-01-28T12:04:42-05:00",
               "file":"yyyy.pdf"
            }           
         ]       
      }
   ]
 }

I am not sure why this doesn't work as I am not sure how else to specify the first element of the trackIssues array ($.trackIssues[0]). I have tried other ways of doing this (JSON_SET) but can't seem to solve it.

Any help would be appreciated including how to create if it doesn't exist or append if it does.

Nick · Accepted Answer · 2020-01-29 08:11:02Z

Update

To append to the array when it already exists, or insert when it doesn't exist, you need to use JSON_CONTAINS_PATH to determine if the array already exists, and, dependent on that, either JSON_INSERT an array as described below, or add to the array using JSON_ARRAY_APPEND. The full query:

UPDATE deals
SET jdeal_status = CASE JSON_CONTAINS_PATH(jdeal_status, 'one', '$.trackIssues[0].attachments')
  WHEN 0 THEN JSON_INSERT(jdeal_status, '$.trackIssues[0].attachments', 
    JSON_ARRAY(
      JSON_OBJECT(
        'date', "2020-01-28T12:04:42-05:00",
        'file', 'yyyy.pdf'
      )
    )
  )
  ELSE JSON_ARRAY_APPEND(jdeal_status, '$.trackIssues[0].attachments',
    JSON_OBJECT(
      'date', "2020-01-28T12:04:42-05:00",
      'file', 'yyyy.pdf'
    )
  )
  END

Demo on dbfiddle

Original answer

What you need to do is JSON_INSERT the attachments array of objects into $trackIssues[0]:

UPDATE deals set jdeal_status = JSON_INSERT(jdeal_status, '$.trackIssues[0].attachments', 
    JSON_ARRAY(
      JSON_OBJECT(
        'date', "2020-01-28T12:04:42-05:00",
        'file', 'yyyy.pdf'
      )
    ) 
)

Demo on dbfiddle

Output from pretty-printing the JSON:

{
    "trackIssues": [
        {
            "date": [
                "2020-01-28T12:04:42-05:00"
            ],
            "notes": [
                {
                    "date": "2020-01-28T12:30:46-05:00",
                    "name": "Joe Robbs",
                    "note": "New note"
                },
                {
                    "date": "2020-01-28T17:45:29-05:00",
                    "name": "John Ha",
                    "note": "New attachment"
                }
            ],
            "value": 3000,
            "isResolved": false,
            "issueIndex": 0,
            "attachments": [
                {
                    "date": "2020-01-28T12:04:42-05:00",
                    "file": "yyyy.pdf"
                }
            ]
        }
    ]
}

Collectives™ on Stack Overflow

How to add a new JSON array field to existing MySQL JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related