Proposal: ABSENT ON TRAILING NULL for JSON_ARRAY

From: Matias Kenig <matias(dot)kenig(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Proposal: ABSENT ON TRAILING NULL for JSON_ARRAY
Date: 2026-11-22 16:11:50
Message-ID: CAKUPQ9tthZ=fuFEy0GFf4Df_v8jejDReHbC4--K8W94=1tAQNg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

I'd like to propose a small extension to the SQL/JSON JSON_ARRAY constructor:
a new null-handling option that omits only the trailing NULL elements.

SELECT JSON_ARRAY(1, NULL, 3, NULL, NULL ABSENT ON TRAILING NULL);
-- [1, null, 3]

Today JSON_ARRAY offers NULL ON NULL and ABSENT ON NULL. ABSENT ON NULL drops
every NULL, which shifts the remaining elements and is unusable when the array
is positional (e.g. a row serialized as an array of column values). The
proposed clause would keep interior NULLs, so positions stay intact, and drop
only the trailing ones.

Benefits:

* Smaller payloads for wide, sparse, positional data: rows with many
trailing NULLs (or values equal to a column default) no longer carry the
padding.
* Consumers can apply a simple, well-known rule: a missing trailing cell
means "no value" or "use the column default". This mirrors conventions in
other languages, e.g. Java's String.split with limit = 0, which drops
trailing empty strings.
* It removes a common workaround: building the array as text and trimming
it with regexp_replace, which is awkward, slow, and easy to get wrong
(quoting, whitespace differences between json and jsonb output).
* It is backward compatible: it is a new optional clause and does not
change the behavior of any existing query.

Would there be interest in something like this? I'm happy to work on a patch
if the approach and the syntax seem reasonable, and I'm open to alternative
naming (e.g. TRIM TRAILING NULLS).

Thanks,
Matías

Browse pgsql-hackers by date

  From Date Subject
Next Message Raja Sai pranav 2026-11-22 16:17:23 Re: Offline data checksum changes can cause incorrect checksum state on standbys
Previous Message Rui Zhao 2026-11-22 15:58:13 Re: Showing "Table Block Fetches" in EXPLAIN ANALYZE for index-only scans