| 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
| 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 |