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.
Best Answer
First the author table, that's easy.
Now we use a self-referential post table to create the hierarchical structure.
Test data...
Recursive query to get it working.
For more information on this method, see my post here