Update column values with dynamically created JSON MySql

I have a Relationships table that looks something like this

ID | FromID | ToID | JsonPair
1      10      8       null
2      11      13      null
3      15      21      null
4      26      22      null

And 2 tables From and To

FromID | FromName                   ToID | ToName
  1        'A'                       1      'Z'                 
  2        'B'                       2      'Y' 
 ...                                ...
  10       'E'                       8      'M'                        
  11       'I'                      ...
 ...                                 13     'N'                        
  15       'O'                      ...
 ...                                 21     'F'                        
  26       'U'                       22     'H'

I’m trying to update the JsonPair column with Json objects in the form {FromName: ToName}. So the resulting table would look like

ID | FromID | ToID | JsonPair
1      10      8     {'E':'M'}
2      11      13    {'I':'N'}
3      15      21    {'O':'F'}
4      26      22    {'U':'H'}

I’m a novice at SQL. I’m thinking that I should SELECT the names first, then use the result to put in the UPDATE statement.

So far I got this to return FromName and ToName

SELECT F.FromName FROM Relationships AS R
JOIN From as F
ON R.FromID = F.FromID

and

SELECT T.ToName FROM Relationships AS R
JOIN To as T
ON R.FromID = T.FromID;

Then I think I should use the result of this to do

UPDATE Relationships
SET JsonPair = (combine result above and format to json)
WHERE JsonPair IS NULL;

I’m stuck at the combining and format step. Can I get help with this please?

I’m using MySql

Answer

You can use the update/join syntax, along with json_object().

Consider:

update relationships r
inner join t_from f on f.fromid = r.fromid
inner join t_to   t on t.to_id  = r.to_id
set r.jsonpair = json_object(f.fromname, t.toname)

Note: from and to are reserved words in MySQL, hence bad choices for table names. I renamed them to t_from and t_to in the query.

Advertisement

Answer