[matthewmacleod]

There are some quite impressive performance gains that can be exploited - especially beneficial if you're using a framework with higher overheads. Using Rails, for example, it's possible to generate JSON output directly from Postgres and stream it to a client, instead of instantiating zillions of ActiveRecord objects just to query flat fields.

There is an obvious trade off of flexibility, but this technique is beneficial in that it allows rich object graphs through the ORM where required, while performing well for simple operations.

[kbenson]

I see that more as working around the limitations of the ORM, not really as an inherent strength of doing it inthe DB. For example, in Perl and using DBIx::Class, I would get around that problem by applying another ResultClass to the ResultSet (the HashRefInflater ResultClass), so instead of inflating results into DBIx::Class objects, it returns a hash directly. With some of the helper classes, this is as simple as changing $result_set->search( \%criteria )->all; to $result_set->search( \%criteria )->hri->all;. This allows you to move the serialization task to the controller, and in what should be a fairly efficient way. I can't imagine ActiveRecord doesn't have something equivalent.

Now, where I could see returning JSON being really useful is if you can build complex structures out of it, in a way that follows constraints. E.g.

    {
      "title" : "Cool Hand Luke",
      "released" : "1967-11-01",
      "cast" : [
        { "name": "Paul Newman" },
        { "name": "George Kennedy" },
        ...
      ],
    }

If that can be efficiently generated on the DB, that could help immensely with the current state of prefetching relationships, which to my knowledge, currently requires either redundant data through joins, and is thus increasingly inefficient the more items you relate (such as with DBIx::Class's prefetch), or uses multiple queries (ActiveRecord's :include), which includes either processing between queries or duplicating portions of results in subsequent queries to get the correct subset of relations.

[treebeard901]

Another advantage from a performance standpoint is that the data used to generate the JSON output is likely already cached in memory by your database engine. With SQL Server (and presumably with Postgres) JSON can be indexed in various ways, leading to even more performance gains.