mysql 动态sql 解析json_使用动态创建的JSON MySql更新列值

我有一个

Relationships

看起来像这样的桌子

ID | FromID | ToID | JsonPair

1 10 8 null

2 11 13 null

3 15 21 null

4 26 22 null

和2张桌子

From

和

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'

我尝试用表单中的Json对象更新JsonPair列

{FromName: ToName}

. 所以得到的表看起来像

ID | FromID | ToID | JsonPair

1 10 8 {'E':'M'}

2 11 13 {'I':'N'}

3 15 21 {'O':'F'}

4 26 22 {'U':'H'}

SELECT

UPDATE

SELECT F.FromName FROM Relationships AS R

JOIN From as F

ON R.FromID = F.FromID

和

SELECT T.ToName FROM Relationships AS R

JOIN To as T

ON R.FromID = T.FromID;

那么我想我应该用这个结果来做

UPDATE Relationships

SET JsonPair = (combine result above and format to json)

WHERE JsonPair IS NULL;

我被困在合并和格式化步骤。请帮我拿这个好吗?

MySql