1

I have a table consists of 3 JSON fields and each of them has data in the format like [1,2,3,4]. I want to find the difference (the elements which are in one field that is not in another field) between any of the two JSON fields using MySQL query (by only using queries).

For example:

field 1 : [1,2,3,4]

field 2 : [1,4]

So the result should be like [2,3]

I am looking for a solution that can be implemented inside a trigger. Please comment if any more details required. Thanks.

Joundill
  • 6,828
  • 12
  • 36
  • 50
Developer Me
  • 11
  • 1
  • Either `LEFT JOIN .. WHERE .. IS NULL` or correlated subquery with `NOT EXISTS`. – Akina May 19 '20 at 07:28
  • Thanks, @Akina I have gone through the possibilities of using joins, but I can't figure out a solution. Since I need to implement this inside a trigger, I used another method of looping each array and comparing elements. Don't know whether this is a perfect solution. – Developer Me May 22 '20 at 04:30
  • JSON arrays must be splitted to rowsets with one value per row ([JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table) may help), then these rowsets may be joined for difference retrieving. But it is too expensive for to use in a trigger. Parse one of your arrays using static service table (which contains consecutive integers from 1 to not less that max. amount of values per array) and [FIND_IN_SET()](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set) for joining. – Akina May 22 '20 at 04:53

0 Answers0