JSON Output in Postgresql

PostgreSQL return result set as JSON array?

TL;DR

SELECT json_agg(t) FROM t

for a JSON array of objects, and

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

for a JSON object of arrays.

List of objects

This section describes how to generate a JSON array of objects, with each row being converted to a single object. The result looks like this:

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

9.3 and up

The json_agg function produces this result out of the box. It automatically figures out how to convert its input into JSON and aggregates it into an array.

SELECT json_agg(t) FROM t

There is no jsonb (introduced in 9.4) version of json_agg. You can either aggregate the rows into an array and then convert them:

SELECT to_jsonb(array_agg(t)) FROM t

or combine json_agg with a cast:

SELECT json_agg(t)::jsonb FROM t

My testing suggests that aggregating them into an array first is a little faster. I suspect that this is because the cast has to parse the entire JSON result.

9.2

9.2 does not have the json_agg or to_json functions, so you need to use the older array_to_json:

SELECT array_to_json(array_agg(t)) FROM t

You can optionally include a row_to_json call in the query:

SELECT array_to_json(array_agg(row_to_json(t))) FROM t

This converts each row to a JSON object, aggregates the JSON objects as an array, and then converts the array to a JSON array.

I wasn't able to discern any significant performance difference between the two.

Object of lists

This section describes how to generate a JSON object, with each key being a column in the table and each value being an array of the values of the column. It's the result that looks like this:

{"a":[1,2,3], "b":["value1","value2","value3"]}

9.5 and up

We can leverage the json_build_object function:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

You can also aggregate the columns, creating a single row, and then convert that into an object:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

Note that aliasing the arrays is absolutely required to ensure that the object has the desired names.

Which one is clearer is a matter of opinion. If using the json_build_object function, I highly recommend putting one key/value pair on a line to improve readability.

You could also use array_agg in place of json_agg, but my testing indicates that json_agg is slightly faster.

There is no jsonb version of the json_build_object function. You can aggregate into a single row and convert:

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Unlike the other queries for this kind of result, array_agg seems to be a little faster when using to_jsonb. I suspect this is due to overhead parsing and validating the JSON result of json_agg.

Or you can use an explicit cast:

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )::jsonb
FROM t

The to_jsonb version allows you to avoid the cast and is faster, according to my testing; again, I suspect this is due to overhead of parsing and validating the result.

9.4 and 9.3

The json_build_object function was new to 9.5, so you have to aggregate and convert to an object in previous versions:

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

depending on whether you want json or jsonb.

(9.3 does not have jsonb.)

9.2

In 9.2, not even to_json exists. You must use row_to_json:

SELECT row_to_json(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Documentation

Find the documentation for the JSON functions in JSON functions.

json_agg is on the aggregate functions page.

Design

If performance is important, ensure you benchmark your queries against your own schema and data, rather than trust my testing.

Whether it's a good design or not really depends on your specific application. In terms of maintainability, I don't see any particular problem. It simplifies your app code and means there's less to maintain in that portion of the app. If PG can give you exactly the result you need out of the box, the only reason I can think of to not use it would be performance considerations. Don't reinvent the wheel and all.

Nulls

Aggregate functions typically give back NULL when they operate over zero rows. If this is a possibility, you might want to use COALESCE to avoid them. A couple of examples:

SELECT COALESCE(json_agg(t), '[]'::json) FROM t

SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t

_{Credit to Hannes Landeholm for pointing this out}

PostgreSQL json formatting

with t (key, value) as ( values
    ('abc', 'john'),
    ('def', 'mary')
)
select json_object(array_agg(key), array_agg(value))
from t
;
           json_object            
----------------------------------
 {"abc" : "john", "def" : "mary"}

Postgres query result to json object

COALESCE(NULL, w.delivery_date) boils down to just w.delivery_date.

Consequently WHERE w.delivery_date = COALESCE(NULL, w.delivery_date) boils down to WHERE w.delivery_date IS NOT NULL.

Count('payload') OVER () AS ROWCOUNT is just a noisy way of saying count(*) OVER () AS rowcount and returns the total row count of the result.

Your current query, simplified:

SELECT payload, count(*) OVER () AS rowcount
FROM   wholesale_confirmation.wholesale_order_confirmation
WHERE  delivery_date          IS NOT NULL
AND    ship_to_location_id    IS NOT NULL
AND    order_raised_date      IS NOT NULL
AND    ship_from_location_id  IS NOT NULL
LIMIT  10;

To get a JSON object like in your updated question, containing one array of JSON objects and the total count of rows:

SELECT json_build_object('payload', jsonb_agg(payload), 'rowcount', min(rowcount))
FROM  (
   SELECT payload, count(*) OVER () AS rowcount
   FROM   wholesale_confirmation.wholesale_order_confirmation
   WHERE  delivery_date          IS NOT NULL
   AND    ship_to_location_id    IS NOT NULL
   AND    order_raised_date      IS NOT NULL
   AND    ship_from_location_id  IS NOT NULL
   LIMIT  10
   ) sub;

Best way to get result count before LIMIT was applied

If you are dealing with many rows, the performance with LIMIT / OFFSET degrades. Consider a more sophisticated pagination technique:

Optimize query with OFFSET on large table

JSON output in Postgresql

There is built-in support for JSON since PostgreSQL 9.2 and it has increased with many other features in more recent versions(For example: JSON functions in PostgreSQL 0.4).

Specially the row_to_json converts a record into a JSON object and the array_to_json turns arrays into JSON arrays.

For example, both functions can be combined to easily turn the results of a SELECT query into JSON:

SELECT array_to_json(array_agg(row_to_json(t))) FROM 
    (SELECT col1, col2, col3 FROM example_table) t

Convert rows into json object in postgresql

You can use jsonb_object_agg()

select jsonb_object_agg(key, value)
from the_table

How to transform a postgresql select with join to a json object?

According to, Postgres document you can use the row_to_json function to transfer row to JSON and then append each table rows with an alias to be detected by row_to_json

with cte as (
  select 
    cm.*,
    e as event,
    u as user
  from 
    chat_messages cm,
    events e,
    users u
  where
    cm.event_id = e.id 
    and cm.user_id = u.id
)
select row_to_json(c) from cte c;

Postgres extract json data with brackets and apostrophe

Your quotes are all over the place. E.g. the second quote (after the opening bracket) closes the string again. Therefore you end up with a "data_adjustment" instruction - and so on ...

Your initial idea, using double quotes, is the right approach here:

select data -> 'root[''data_adjustment''][''timestamp'']' -> 'new_value' as new_value,
       data -> 'root[''data_adjustment''][''timestamp'']' -> 'old_value' as old_value

Filtering the JSON data stored in Postgres based on key-value pairs

USING gin ((cart->'items') jsonb_path_ops);

SELECT * FROM order2 WHERE cart->'items' @> '[{"name":"Trousers"}]';

Refrences :

Query for array elements inside JSON type

Index for finding an element in a JSON array

PL/pgSQL rows to json array

Give this a try:

SELECT JSON_AGG(src) AS my_json_array
FROM (
  SELECT 
    users.id, 
    users.name, 
    users.surname, 
    users.fkrole, 
    users.username 
  FROM users
) src
;

This will give you all the rows from your "src" query returned as a single json array.

http://www.sqlfiddle.com/#!17/6241a/2

Here's some more info on the Postgres JSON functions: https://www.postgresql.org/docs/10/static/functions-json.html