doc: mention ORDER BY for some aggregates, add ORDER BY examples

Discussion: https://postgr.es/m/CAKFQuwb+4SWnfrfQKB-UM1P1x97Xk+ybSar4xM32XGLd=fq9bA@mail.gmail.com

Co-authored-by: David G. Johnston

Backpatch-through: master

2 files changed, 43 insertions, 21 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5d5ad7ee6ac..a6fcac0824a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20288,6 +20288,13 @@ SELECT NULLIF(value, '(none)') ...
    aggregation.
   </para>
 
+  <para>
+   While all aggregates below accept an optional
+   <literal>ORDER BY</literal> clause (as outlined in <xref
+   linkend="syntax-aggregates"/>), the clause has only been added to
+   aggregates whose output is affected by ordering.
+  </para>
+
    <table id="functions-aggregate-table">
     <title>General-Purpose Aggregate Functions</title>
     <tgroup cols="2">
@@ -20325,7 +20332,7 @@ SELECT NULLIF(value, '(none)') ...
         <indexterm>
          <primary>array_agg</primary>
         </indexterm>
-        <function>array_agg</function> ( <type>anynonarray</type> )
+        <function>array_agg</function> ( <type>anynonarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>anyarray</returnvalue>
        </para>
        <para>
@@ -20336,7 +20343,7 @@ SELECT NULLIF(value, '(none)') ...
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <function>array_agg</function> ( <type>anyarray</type> )
+        <function>array_agg</function> ( <type>anyarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>anyarray</returnvalue>
        </para>
        <para>
@@ -20541,14 +20548,14 @@ SELECT NULLIF(value, '(none)') ...
         <indexterm>
          <primary>json_agg</primary>
         </indexterm>
-        <function>json_agg</function> ( <type>anyelement</type> )
+        <function>json_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>json</returnvalue>
        </para>
        <para role="func_signature">
         <indexterm>
          <primary>jsonb_agg</primary>
         </indexterm>
-        <function>jsonb_agg</function> ( <type>anyelement</type> )
+        <function>jsonb_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>jsonb</returnvalue>
        </para>
        <para>
@@ -20588,7 +20595,8 @@ SELECT NULLIF(value, '(none)') ...
         </indexterm>
         <function>json_object_agg</function> ( <parameter>key</parameter>
          <type>"any"</type>, <parameter>value</parameter>
-         <type>"any"</type> )
+         <type>"any"</type>
+         <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>json</returnvalue>
        </para>
        <para role="func_signature">
@@ -20597,7 +20605,8 @@ SELECT NULLIF(value, '(none)') ...
         </indexterm>
         <function>jsonb_object_agg</function> ( <parameter>key</parameter>
          <type>"any"</type>, <parameter>value</parameter>
-         <type>"any"</type> )
+         <type>"any"</type>
+         <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>jsonb</returnvalue>
        </para>
        <para>
@@ -20834,7 +20843,8 @@ SELECT NULLIF(value, '(none)') ...
        </para>
        <para role="func_signature">
         <function>string_agg</function> ( <parameter>value</parameter>
-         <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type> )
+         <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type>
+         <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>bytea</returnvalue>
        </para>
        <para>
@@ -20892,7 +20902,7 @@ SELECT NULLIF(value, '(none)') ...
         <indexterm>
          <primary>xmlagg</primary>
         </indexterm>
-        <function>xmlagg</function> ( <type>xml</type> )
+        <function>xmlagg</function> ( <type>xml</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
         <returnvalue>xml</returnvalue>
        </para>
        <para>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 3ba844057fb..9cee925a483 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1647,7 +1647,20 @@ sqrt(2)
     are always just expressions and cannot be output-column names or numbers.
     For example:
 <programlisting>
-SELECT array_agg(a ORDER BY b DESC) FROM table;
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(v ORDER BY v DESC) FROM vals;
+  array_agg
+-------------
+ {4,3,3,2,1}
+</programlisting>
+    Since <type>jsonb</type> only keeps the last matching key, ordering
+    of its keys can be significant:
+<programlisting>
+WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
+SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
+      jsonb_object_agg
+----------------------------
+ {"key0": "1", "key1": "3"}
 </programlisting>
    </para>
 
@@ -1668,20 +1681,19 @@ SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrect
    </para>
 
    <para>
-    If <literal>DISTINCT</literal> is specified in addition to an
-    <replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal>
-    expressions must match regular arguments of the aggregate; that is,
-    you cannot sort on an expression that is not included in the
-    <literal>DISTINCT</literal> list.
+    If <literal>DISTINCT</literal> is specified with an
+    <replaceable>order_by_clause</replaceable>, <literal>ORDER
+    BY</literal> expressions can only reference columns in the
+    <literal>DISTINCT</literal> list.  For example:
+<programlisting>
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
+ array_agg
+-----------
+ {4,3,2,1}
+</programlisting>
    </para>
 
-   <note>
-    <para>
-     The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal>
-     in an aggregate function is a <productname>PostgreSQL</productname> extension.
-    </para>
-   </note>
-
    <para>
     Placing <literal>ORDER BY</literal> within the aggregate's regular argument
     list, as described so far, is used when ordering the input rows for