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 data
FROM 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
    )
) res
FROM 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 ed
WHERE ed.effortId = e.id

As:

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