How to migrate a JSON table that contains an array to SQL?

You can leverage MySQL's JSON functions to generate a nested JSON structure directly from the database.

Consider:

SELECT     id,     userId,     effortDate,     createdAt,     updatedAt,    (        SELECT JSON_ARRAYAGG(            JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)        )        FROM efforts_data ed        WHERE ed.effortId = e.id    ) AS dataFROM efforts

This gives you one row per efforts, with a column called data that contains a JSON payload made of an array of objects coming table efforts_data.

You can go one step forward and stuff each row in a single object, if that's what you want:

SELECT JSON_OBJECT(    'id', id,     'userId', userId,     'effortDate', effortDate,     'createdAt', createdAt,     'updatedAt', updatedAt,    'data', (        SELECT JSON_ARRAYAGG(            JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)        )        FROM efforts_data ed        WHERE ed.effortId = e.id    )) resFROM efforts

In MySQL < 5.7.22 or MariaDB < 10.5.0, where JSON_ARRAYAGG() is not yet available, one workaround is GROUP_CONCAT() and string concatenation. Basically you would rewite this:

SELECT JSON_ARRAYAGG(           JSON_OBJECT('projectId', ed.projectId, 'value', ed.value))FROM efforts_data edWHERE ed.effortId = e.id

As:

SELECT CONCAT(    '[',     GROUP_CONCAT(JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)),    ']')FROM efforts_data edWHERE ed.effortId = e.id

My question is: is there any way to do it using only SQL?

Yes, that's why you have the relationship between efforts id and efforts_data effortId:

SELECT e.id, e.userId, e.effortDate, e.createdAt, e.updatedAt,       ed.id, ed.effortId, ed.projectId, ed.valueFROM efforts AS e, efforts_data AS edWHERE e.id = ed.effortId