You can still think of data as 'rows' with MongoDB - objects belong to a collection, which is analogous to a table - and you can query and select only specific fields.
The advantage is that you aren't nearly as restricted in the kinds of data you can build. SQL often forces you to split logically related data across several tables and wrestle with complicated joins, simply because a row can't contain sub-arrays or hashes.
Of course, there's no silver bullet - some times you DO want to group together logically different data in a same query. But I've found that in general, I'm not fighting the DB as much when building stuff using MongoDB.
Have you actually looked at or used the array stuff in postgres? It's pretty horrible syntactically and worse, very explicit in it's recommended use.
"Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements."
Searching an array is a pretty common task, Mongo does really well in its ability to search into objects in a document.
I had to laugh a little at one of the sample queries:
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
Yes I've looked at them. I use them every day.
The example you posted its difficult to parse because it deals with multidimensional arrays and also populating one in the inner query. The simple, and much more common case of one dimension arrays is very straightforward. Can you show me the equivalent in MongoDB?
About the performance/scalability warning; I don't deal with very large arrays, a couple hundred items max, and when using a GIN index over the array field, search queries are screamingly fast.
Of course, there's no silver bullet - some times you DO want to group together logically different data in a same query. But I've found that in general, I'm not fighting the DB as much when building stuff using MongoDB.