DEV Community

loading...

Discussion on: PostgreSQL vs MongoDB

rhymes profile image
rhymes

This is a single query merging multiple records from different tables.

I'm not sure we're talking about different things though. I think it's a matter of perspective. An API that queries multiple data sources and then returns them to you as a result of a single query is to you, the caller, a single query.

PostgreSQL and other DBs splits queries in multiple parallel fetches sometimes, they physically make multiple reads at the same time, but does it matter to you? No, because with you issue one SQL query to get all the data you need.

Moving on to your example, you can already do it:

PracticalDeveloper_development> select count(*) from comments where user_id = 10;
-[ RECORD 1 ]-------------------------
count | 3
SELECT 1
Time: 0.008s
PracticalDeveloper_development> select users.id, users.username, array_agg(row_to_json(row)) as comments from (select c.* from comments c where c.user_id = 10) row, users group b
 y users.id having users.id = 10;

-[ RECORD 1 ]-------------------------
id       | 10
username | lockmanjerry
comments | ['{"id":17,"ancestry":null,"body_html":null,"body_markdown":"You probably haven\'t heard of them chartreuse direct trade. Humblebrag photo booth marfa. Gluten-free goth flannel 90\'s 8-bit vice.","commentable_id":24,"commentable_type":"Article","created_at":"2020-06-22T11:18:56.028987","deleted":false,"edited":false,"edited_at":null,"hidden_by_commentable_user":false,"id_code":"h","markdown_character_count":131,"positive_reactions_count":0,"processed_html":"<p>You probably haven\'t heard of them chartreuse direct trade. Humblebrag photo booth marfa. Gluten-free goth flannel 90\'s 8-bit vice.</p>\\n\\n","public_reactions_count":1,"reactions_count":0,"receive_notifications":true,"score":1,"spaminess_rating":0,"updated_at":"2020-06-22T11:34:28.673251","user_id":10}', '{"id":19,"ancestry":null,"body_html":null,"body_markdown":"Sriracha shoreditch pitchfork offal selfies. Knausgaard godard pabst fixie microdosing mixtape meggings.","commentable_id":21,"commentable_type":"Article","created_at":"2020-06-22T11:18:56.218676","deleted":false,"edited":false,"edited_at":null,"hidden_by_commentable_user":false,"id_code":"j","markdown_character_count":104,"positive_reactions_count":0,"processed_html":"<p>Sriracha shoreditch pitchfork offal selfies. Knausgaard godard pabst fixie microdosing mixtape meggings.</p>\\n\\n","public_reactions_count":1,"reactions_count":0,"receive_notifications":true,"score":1,"spaminess_rating":0,"updated_at":"2020-06-22T11:34:28.883918","user_id":10}', '{"id":15,"ancestry":null,"body_html":null,"body_markdown":"Humblebrag austin wayfarers wes anderson cardigan celiac organic pork belly. Irony butcher tacos pork belly normcore retro whatever you probably haven\'t heard of them. Small batch meh etsy pork belly williamsburg sartorial schlitz yolo.","commentable_id":3,"commentable_type":"Article","created_at":"2020-06-22T11:18:55.850519","deleted":false,"edited":false,"edited_at":null,"hidden_by_commentable_user":false,"id_code":"f","markdown_character_count":236,"positive_reactions_count":0,"processed_html":"<p>Humblebrag austin wayfarers wes anderson cardigan celiac organic pork belly. Irony butcher tacos pork belly normcore retro whatever you probably haven\'t heard of them. Small batch meh etsy pork belly williamsburg sartorial schlitz yolo.</p>\\n\\n","public_reactions_count":1,"reactions_count":0,"receive_notifications":true,"score":3,"spaminess_rating":0,"updated_at":"2020-06-22T11:34:28.444569","user_id":10}']
SELECT 1
Time: 0.008s

User 10 has 3 comments, I selected them all with one query and aggregated them in an array of JSON rows.

I'm sure the query can be simplified with a CTE or other clever tricks but it's a decent start.

I can understand why it's a little bit more complicated to do with noSQL DBs. They usually have more complicated ways of putting data in relation to other data.

Thread Thread
necmettin profile image
Necmettin Begiter

Yes, multiple queries resulting in a single resultset is, in the end, a single call for the client, but on the server side, it means multiple calls. You are right of course, but I'm a little unforgiving I guess :)

Yeah, PostgreSQL almost got it, except JSON in Postgres are strings. Almost there :)

What can I say, I'm a grumpy old programmer, it's not easy to satisfy me. ;)

Cheers.

Thread Thread
rhymes profile image
rhymes

Yeah, PostgreSQL almost got it, except JSON in Postgres are strings. Almost there :)

Not really though: JSON in PostgreSQL is just JSON:

PracticalDeveloper_development> select id, username, email_public from users limit 1;
+------+-----------------+----------------+
| id   | username        | email_public   |
|------+-----------------+----------------|
| 6    | rosenbaumladawn | False          |
+------+-----------------+----------------+
SELECT 1
Time: 0.016s
PracticalDeveloper_development> select row_to_json(t) from (select id, username, email_public from users limit 1) t;
+------------------------------------------------------------+
| row_to_json                                                |
|------------------------------------------------------------|
| {"id":6,"username":"rosenbaumladawn","email_public":false} |
+------------------------------------------------------------+
SELECT 1
Time: 0.015s

Integers are integers, strings are strings and booleans are booleans

Thread Thread
necmettin profile image
Necmettin Begiter

When PostgreSQL came up with the JSON features, I remember reading examples and always seeing 'some-json-formatted-data'::JSON, so I assumed JSON data is given to / received from PostgreSQL in a string. Even the comments field in your example a few comments back (the one with the 3 comments of user 10) has the JSON data in strings.

If that isn't the case, my bad, I didn't know PostgreSQL had progressed this far, I thought it was still using strings to encapsulate/represent JSON data contained in cells.

Forem Open with the Forem app