From nested tables to JSON

I am beginning to KNIME and I am starting a proof of concept for database migration.

I have a relational database with 3 connected tables like this:

  • Post (ID, TITLE)
  • Comment (POST_ID, DATE, TEXT)
  • Link (POST_ID, HREF)

I would like to read the database and generate JSON files like this (one file per Post)

{
   "post_id": "1",
   "title": "Post 1",
   "comments": [
      { "date":"2020-01-01", "text": "Comment 1" }, 
      { "date":"2020-01-02", "text": "Comment 2" }
   ],
   "links": [
     { "href": "Link 1" },
     { "href": "Link 2" }
   ]
}

Which nodes should help me to solve this?

Suppose the database structure can be much more complex than described above (several branches and levels).

This is what I am trying (did not execute yet)

Would it make more sense to read in your database tables and do necessary joining and ETL before your loop? If I understand you right, you only really need to loop around the JSON generation for each post.

Do you have a sample file?
bR