Alternatively, supplying the input values from a sorted subquery will usually work. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. The aggregate functions array_agg, json_agg, jsonb_agg, json_agg_strict, jsonb_agg_strict, json_object_agg, jsonb_object_agg, json_object_agg_strict, jsonb_object_agg_strict, json_object_agg_unique, jsonb_object_agg_unique, json_object_agg_unique_strict, jsonb_object_agg_unique_strict, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. The coalesce function can be used to substitute zero or an empty array for null when necessary. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. It should be noted that except for count, these functions return a null value when no rows are selected. Sum ( double precision ) → double precisionĬomputes the sum of the non-null input values.Ĭoncatenates the non-null XML input values (see Section 9.15.1.7). Each value after the first is preceded by the corresponding delimiter (if it's not null). String_agg ( value bytea, delimiter bytea ) → byteaĬoncatenates the non-null input values into a string. String_agg ( value text, delimiter text ) → text Values are converted to JSON as per to_json or to_jsonb. Range_intersect_agg ( value anymultirange ) → anymultirangeĬomputes the intersection of the non-null input values.Ĭollects all the input values, skipping nulls, into a JSON array.

Range_intersect_agg ( value anyrange ) → anyrange Range_agg ( value anymultirange ) → anymultirangeĬomputes the union of the non-null input values.

Range_agg ( value anyrange ) → anymultirange Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, xid8, and arrays of any of these types. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, xid8, and arrays of any of these types.Ĭomputes the minimum of the non-null input values. If there is a duplicate key an error is thrown.Ĭomputes the maximum of the non-null input values. If the value is null then the entry is skipped. Key arguments are coerced to text value arguments are converted as per to_json or to_jsonb. Jsonb_object_agg_unique_strict ( key 'any', value 'any' ) → jsonbĬollects all the key/value pairs into a JSON object. Json_object_agg_unique_strict ( key 'any', value 'any' ) → json SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → If ORDER BY is specified, the elements will appear in the array in that order rather than in the input order. If ABSENT ON NULL is specified, any NULL values are omitted. Json_objectagg ( ] ])īehaves in the same way as json_array but as an aggregate function so it only takes one value_expression parameter. This is the SQL standard's equivalent to bool_and.Ĭollects all the input values, including nulls, into a JSON array. Returns true if any non-null input value is true, otherwise false.Ĭomputes the number of input rows in which the input value is not null. Returns true if all non-null input values are true, otherwise false. Can be useful as a checksum for an unordered set of values. (The inputs must all have the same dimensionality, and cannot be empty or null.)Īvg ( double precision ) → double precisionĬomputes the average (arithmetic mean) of all the non-null input values.Ĭomputes the bitwise AND of all non-null input values.Ĭomputes the bitwise OR of all non-null input values.Ĭomputes the bitwise exclusive OR of all non-null input values. Returns an arbitrary value from the non-null input values.Ĭollects all the input values, including nulls, into an array.Ĭoncatenates all the input arrays into an array of one higher dimension. Any_value ( anyelement ) → same as input type

Json functions in postgresql