What is the best way to transfer the nested JSON data of a HTTP response into a SQL database?

Question

I am creating an API with Node.js, Fastify and Postgresql, this is supposed to get the data of a form from a yet to be developed React client, I imagine the structure of the data that will eventually arrive in my API as follows:

{
"quote": {
"body": "Of our qualities, none can be ascribed to the divine being without tainting it with its imperfection.",
"author": "michel de montaigne",
"page": "55",
"book": {
"title": "Diary of the Journey to Italy",
"publisher": "Eichborn Verlag",
"year": "2009"
        }
    },
"hashtag": "#God",
"user": {
"name": "betonhead",
"email": "bier@wurst.de"
    }
}

Now I want to store these values in my Postgresql database, for this I need to split the data to the different tables (authors, quotes, books, user, etc.) that are related to each other.

In my research on how to implement the JSON data I want to use as input to my API file I found two concepts:

1. creating a view in Postgres, splitting the data via Node.js middleware.

2. store the raw JSON files directly in Postgres and from there distribute them to the tables using a function.

My question now is which method is preferable here, or maybe there are other better methods?

Thanks for your help.

Alex · Answer 1 · 2020-12-17T02:48:17.553

"The best way" depends on the specific constraints/requirements of your particular scenario. However, given that; a) PostgreSQL is used to store "normalized[1]" data, b) the data represented in your JSON payload relates to multiple entities, c) you don't anticipate the shape/structure of the data changing often, and d) the current API call can adequately incur the overhead of deserializing the JSON into appropriate intermediate objects/entities in order to persist them, then I'd advise on performing this work in the application layer — saving the entities accordingly in their normalized representations. However, if lower latency is more desirable, than it is to avoid eventual consistency[2] issues, you could opt for an approach that dumps the payload to some intermediately durable store (like a Message Queue) for future processing into PostgreSQL.

Another alternative would be to use a Document Database like MongoDB.

NB: jsonb[3] could be an option if you expect the payload to change frequently, and/or the shape of specific fields is dynamic/indeterminate. jsonb indexes and stores data in binary format, with associated metadata along with every record. Don't think this would be the best approach for this scenario, but it's worth mentioning.

More Reading:

What is the best way to transfer the nested JSON data of a HTTP response into a SQL database?

1 Answers1