I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts
table in Postgres, I have a JSONB[]
array holding comments for the post.
The thing is that each element in the array only holds an id
of the comment author, not the actual user info of the author. What would be the best way to perform a join on every element of that array to replace the author id
for their data, or if not what would be a better way to do this?
For example, one of my post table rows might look like:
id: 5
author: 1
body: "Hello word"
comments: [{author:0, body: "It's me"}]
Where the comments column is a JSONB
array.
My users table might look like:
id: 0
username: Dux
I do need threaded comments.