-1

I'm fetching data from Clockify, flattening the JSON response, and storing it in the database where each property on the API response should have its own table's field.

I have read this article about how can I achieve what I'm trying to do, however in my JSON response I have tagsIds which looks like an array, and I'm looking for the best way to store them in the database.

I think I can give tagIds to DB column name, and store the tags separated by a comma. Is it a good practice or should I store them another way?

[
{
    "id": "5fd1c9dfb9189912e857bc7b",
    "description": "",
    "tagIds": [
        "5fd1c9ebb54ab12970cf0f20",
        "5fd1c9f2b54ab12970cf0f5b"
    ],
    "userId": "5fd1c922b9189912e857b563",
    "billable": true,
    "taskId": null,
    "projectId": "5fd1c9d7b9189912e857bc1e",
    "timeInterval": {
        "start": "2020-12-10T07:10:22Z",
        "end": "2020-12-10T07:10:46Z",
        "duration": "PT24S"
    },
    "workspaceId": "5fd1c923b9189912e857b566",
    "isLocked": false,
    "customFieldValues": null
}]
1
1

Store it as comma-separated list is discouraged because

  • hard to ensure data type for each element, e.g, 5fd1c9ebb54ab12970cf0f20,false, 5fd1c9f2b54ab12970cf0f5b
  • hard to guarantee the uniqueness of elements in the list
  • may fail to store many tags since it could be longer than the string column
  • hard to do query, join, and count operations on the list

You can store each tagId as one row in a separate table instead, with a primary key of (id, tagId):

+-------------+------------------------+----------------
|            id             |        tagId             |
+-------------+------------------------+---------------|
|  5fd1c9dfb9189912e857bc7b | 5fd1c9ebb54ab12970cf0f20 |
|  5fd1c9dfb9189912e857bc7b | 5fd1c9f2b54ab12970cf0f5b |

Other than that, worth looking at JSON_TABLE in mysql, and Arrays type support in Postgres.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.