How to get json from postgres select

In short: it's pretty easy (and pretty useful!).

All you need is to know how to use row_to_json(), array_to_json() and aggregation functions.

The simplest issue — to conevrt entire row into json. We can do it with row_to_json() function:

 select row_to_json(orders) from orders;
	

This query puts in json only one row. Ok, now let's get a list of entries:

 select array_to_json(array_agg(row_to_json(orders))) from orders;
	

And one more example of how to get list of related entries as a json list:

 select orders.*, array_to_json(array_agg(row_to_json(operations))) as operations 
 from orders 
 join operations on operations.order_id = orders.id 
 group by orders.id;