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

Question

I'm a trainee front-end developer. Never really liked back-end so I know very little about SQL.

But here we are, in one of those adventuring times that I need to do the backend because I was assigned too. Well, we have an internal system for tracking some data about ourselves that we are rebuilding. The previous system used a JSON File to store its data, in a format like this:

{
  "id": 123,
  "userId": 522,
  "effortDate": "2020-06-05",
  "data": [
    {
      "projectId": 14,
      "value": 7
    },
    {
      "projectId": 23,
      "value": 3
    }
  ],
  "updatedAt": "2020-06-08T10:13:11-03:00",
  "createdAt": "2020-06-08T10:13:11-03:00"
}

We are moving to SQL based database. So, with my basic understanding of how relational databases works,

I've built this model:

.

The problem is: at least for now, we are using the same front-end for the application, so I need the data parsed in the same format as it was back then when it was only a JSON. And for the basic operation of the system, it needs to require all the data from the now, two tables, all the time.

My first idea, was to kind mix them up, you know, by force, with two different SELECT's. Which I guess, isn't that bad (maybe because it works), except we're dealing with thousands of entries, so I'm scared things might get slow. Here's the code I used to parse it up:

public function getAllEfforts() {
  /* Get all data from `effort` */
  $effortQuery = "SELECT id, userId, effortDate, createdAt, updatedAt
                  FROM efforts;";

  $efforts = $this->pdo
    ->query($effortQuery)
    ->fetchAll(PDO::FETCH_ASSOC);

  /* Get all data from `effort_data` */
  $effortDataQuery = "SELECT id, effortId, projectId, value
                      FROM efforts_data;";

  $effortsData = $this->pdo
    ->query($effortDataQuery)
    ->fetchAll(PDO::FETCH_ASSOC);

  /* Since, `effort` doesn't have a data field, let's define the array wished here */
  foreach($efforts as &$effortsUnity) {
    $effortsUnity['data'] = [];
  }

  /* Push the stuff from `effort_data` to the just created data array */
  foreach($effortsData as &$effortDataUnity) {
    $effortIndex = ($effortDataUnity['effortId'] - 1);

    unset($effortDataUnity['id']);
    unset($effortDataUnity['effortId']);

    array_push(
      $efforts[$effortIndex]['data'],
      $effortDataUnity
    );
  }
  
  return $efforts;
}

My question is: is there any way to do it using only SQL? Or any other optimal way of doing this besides using MongoDB or other JSON based DB which is not possible at the moment.

Sorry If I was not clear enough, it is my first question here, I'm willing to clarify any questions.

Thanks a lot.

Answer 1

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