I would like to pass data from a table with several columns to another table in a single column

Question

good afternoon / night, let me tell you, I have a database with several tables and one of them is obsolete since I am trying to pass it to another. I put you in situation. I have the user_accounts table with the following format. "image below"

I would like to know if that information can be selected and inserted into another table "users" with this format. "link below"

It would be a search for the identifier and the same update for the identifier.

Thanks in advance.

Answer 1

Use JSON_OBJECTAGG()

UPDATE users AS u
JOIN (
    SELECT identifier, JSON_OBJECTAGG(name, money) AS accounts
    FROM user_accounts
    GROUP BY identifier
) AS a ON u.identifier = a.identifier
SET u.accounts = a.accounts