Fix jsonb_object_agg() and related aggs used as window functions. When being used as a window function, these aggregates must run the finalization step repeatedly on their accumulated state. During that, discarding null-valued pairs isn't okay if we want to reject duplicate keys, since per SQL spec a null-valued pair still participates in duplicate key checking. Hence, when both those options are selected (jsonb_object_agg_unique_strict) run uniqueifyJsonbObject on a copy of the pairs array, keeping the original data structure unmodified. We don't want to do that when unique_keys isn't specified, because repeat de-duplication can be very expensive when there are many duplicates, but fortunately we don't have to.
uniqueifyJsonbObject did not renumber the pairs it kept, so a pair added later could lose to an older one with the same key. Renumber them to ensure that we preserve last-one-wins behavior across multiple finalizations. Also improve related comments. While adding test cases to cover these mistakes, I chose to sync up all the json[b]_object_agg tests in json.sql and jsonb.sql. This exposed that json_object_agg() fails to de-duplicate keys in its output, which seems like a pre-existing bug; but for this commit let's just memorialize its behavior. Author: shihao zhong <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/CAGRkXqTCfSu=aEUCvKDTGroEWOcyz3MCv4Xi8x1PZ1T1rEmG=q...@mail.gmail.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/56b463f373c644c321f938ae9db30a7c4a66cd58 Modified Files -------------- src/backend/utils/adt/jsonb.c | 24 ++++++++++++-- src/backend/utils/adt/jsonb_util.c | 24 +++++++++----- src/test/regress/expected/json.out | 65 +++++++++++++++++++++++++++++++++++++ src/test/regress/expected/jsonb.out | 20 ++++++++++++ src/test/regress/sql/json.sql | 21 ++++++++++++ src/test/regress/sql/jsonb.sql | 11 +++++++ 6 files changed, 153 insertions(+), 12 deletions(-)
