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:

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.

GMB · Accepted Answer · 2020-08-05 22:42:08Z

4

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

edited Aug 5, 2020 at 22:42

answered Aug 5, 2020 at 22:02

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Gabriel Andrade Over a year ago

Thanks a lot. I'd didn't even know MySQL had built-in JSON features. I'm looking out for those. I'm getting an PDO Exception, though, ("#1305 - FUNCTION sistema_do_peru.JSON_ARRAYAGG does not exist"). Maybe it could just be my MySQL version, I'll try to fix it myself. Thanks for showing me the way!

GMB Over a year ago

@GabrielAndrade: which version of MySQL are you running? JSON_ARRAYAGG() was introduced in 5.7.22. select version() can tell you your current version.

biesior Over a year ago

JSON_ARRAYAGG was added in MariaDB 10.5.0. see documentation

biesior Over a year ago

@GabrielAndrade BTW you can install MySQL per se separately and run it instead of MariaDB from XAMPP, it's how I'm working now, as MariaDB had other pitfalls.

GMB Over a year ago

@GabrielAndrade: I added a workaround for your MariaDB version.

|

AbraCadaver · Accepted Answer · 2020-08-05 22:56:11Z

0

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.value
FROM efforts AS e, efforts_data AS ed
WHERE e.id = ed.effortId

answered Aug 5, 2020 at 22:56

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

2 Comments

nbk Over a year ago

comma seperated tables are long time out of use,because this is basically a crossjoin, where filter after joining, make a prper join

Paul T. Over a year ago

I'm not a fan of what I call "WHERE-clause joins" either.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related