MySQL JSON merge two json objects primary key in JSON document

Question

I've got JSON column storing a document. I want to perform atomic UPDATEs on this column.

Given e.g. value:

[{"substanceId": 182, "text": "substance_name_182"}, {"substanceId": 183, "text": "substance_name_183"}]

and the update

[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]

I expect to get this:

[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 183, "text": "substance_name_183"} {"substanceId": 184, "text": "substance_name_184"}}]

None of the JSON_MERGE_PATCH or JSON_MERGE_PRESERVE directly allow me to achieve it. The JSON_MERGE_PATCH is not aware substanceId being an ID of the document. Is there any way to achieve it on the MySQL side? I could do it client-side (fetch value first, modify and update it back) but that's last resort, firstly I have lots of rows to update, where all of them would be covered by UPDATE with WHERE clause, so the MySQL way would be more convenient. When doing it client-side and do it safely I would have to LOCK many rows FOR UPDATE.

E.g. query:

SELECT JSON_MERGE_PATCH(
'[{"substanceId": 182, "text": "substance_name_182"}, {"substanceId": 183, "text": "substance_name_183"}]',
'[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]'
) v;

results

[{"text": "substance_name_182_new", "substanceId": 182}, {"text": "substance_name_184", "substanceId": 184}]

Out of curiosity, why are you using JSON instead of storing the data as normal rows and columns? It would be much easier to just use INSERT...ON DUPLICATE KEY UPDATE. — Bill Karwin
– Bill Karwin, Commented Mar 9, 2020 at 15:57
@BillKarwin It's useful for me to "dump" the state directly from React front-end and store it in JSON format. This means that if I ever need to recreate whatever user had on the screen I can return the JSON directly back to him. If I normalize it to rows and columns then each time user wants to update his data then I also need to denormalize it. It is pretty painful to add new fields, as you have front-end to modify, then back-end, but also the normalization and denormalization methods. I've decided to give the JSON try, as it fits the React model nicely. — Tom Raganowicz
– Tom Raganowicz, Commented Mar 9, 2020 at 16:04
Thanks for that information. I'm interested in why developers choose to store data in JSON format. Yes, JSON makes it easy to dump semi-structured data into a database, and it's easy to fetch the exact same JSON blob as-is. But as you're finding out, if you need to do partial updates or anything else, it becomes quite difficult. — Bill Karwin
– Bill Karwin, Commented Mar 9, 2020 at 16:19

Nick · Accepted Answer · 2020-03-09 13:37:25Z

2

Here is an insanely convoluted way of doing this in MySQL, using JSON_TABLE to convert the update and original JSON values into columns, merging the columns using a (simulated) FULL JOIN and then re-creating the output JSON value using JSON_OBJECT and JSON_ARRAYAGG; finally using that to update the original table:

WITH upd AS (
  SELECT *
  FROM JSON_TABLE('[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]',
                  '$[*]' COLUMNS (
                  substanceId INT PATH '$.substanceId',
                  txt VARCHAR (100) PATH '$.text')
                  ) jt
  CROSS JOIN (SELECT DISTINCT id
              FROM test) t
),
cur AS (
  SELECT id, substanceId, txt
  FROM test
  JOIN JSON_TABLE(test.j,
                  '$[*]' COLUMNS (
                  substanceId INT PATH '$.substanceId',
                  txt VARCHAR (100) PATH '$.text')
                 ) jt
),
allv AS (
  SELECT COALESCE(upd.id, cur.id) AS id, 
         COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
         COALESCE(upd.txt, cur.txt) AS txt
  FROM upd
  LEFT JOIN cur ON cur.substanceId = upd.substanceId
  UNION ALL 
  SELECT COALESCE(upd.id, cur.id) AS id, 
         COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
         COALESCE(upd.txt, cur.txt) AS txt
  FROM upd
  RIGHT JOIN cur ON cur.substanceId = upd.substanceId
),
obj AS (
  SELECT DISTINCT id, JSON_OBJECT('substanceId', substanceId, 'text', txt) AS o
  FROM allv
),
arr AS (
  SELECT id, JSON_ARRAYAGG(o) AS a
  FROM obj
  GROUP BY id
)
UPDATE test
JOIN arr ON test.id = arr.id
SET test.j = arr.a
;
SELECT JSON_PRETTY(j)
FROM test

Output:

[
  {
    "text": "substance_name_183",
    "substanceId": 183
  },
  {
    "text": "substance_name_184",
    "substanceId": 184
  },
  {
    "text": "substance_name_182_new",
    "substanceId": 182
  }
]

Demo on dbfiddle

Note this assumes you use a unique id value to distinguish the rows in your table. If you use something else, you would need to swap that in where id is used in the above query.

answered Mar 9, 2020 at 13:37

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Tom Raganowicz Over a year ago

Thank you. Even though it is valid answer, it isn't something I consider to do in my application as the solution is too complicated reducing code clarity and making potential maintenance harder. What are the other options I have? I am considering changing data structure, so the substanceId is the key of the object and the text is either value or object with property ` text` itself. This would allow me to use native JSON_MERGE_PATCH function. How other databases solved this problem? This seem like a basic thing to do, yet pretty hard with MySQL at least.

Nick Over a year ago

@NeverEndingQueue To be honest, I didn't expect you would use it. It was more of a demo to show how painful it is to deal with JSON internally in MySQL. If you just make the table have substanceId and text columns, you can still insert directly from a JSON and read back as JSON in a much easier manner. See dbfiddle.uk/…

Collectives™ on Stack Overflow

MySQL JSON merge two json objects primary key in JSON document

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related