Thread: Re: BUG #16022: to_json on arrays with unusual lower bound is notintuitive
Re: BUG #16022: to_json on arrays with unusual lower bound is notintuitive
From
Andreas Bergmaier
Date:
Hi! On 25.09.19 16:34, Tom Lane wrote: > PG Bug reporting form <noreply@postgresql.org> writes: >> I would expect the array indices of a JSON array to match with the >> subscripts of a postgres array when converting them back and forth. >> However, Postgres lets the JSON array begin at the `lower_bound` of the >> array, not at subscript 1 (= index 0). >> For example, `to_json('[-2:5]={-2,-1,0,1,2,3,4,5}'::int[])` results in >> `[-2,-1,0,1,2,3,4,5]` where I would have expected `[1,2,3,4,5]` (or an >> error, since JSON arrays must not have negative indices). > > I can see no reason whatever for either of those definitions to be > better than the established one. If you want some other conversion > rule, write your own function that behaves the way you want. OK, I agree it's kinda unreasonable to change this. However, I would like to see this behaviour documented at https://www.postgresql.org/docs/current/functions-json.html, so FUP'd to pgsql-docs. I propose to add the following description to Table 9.45: | The resulting JSON array starts at the lower bound of the PostgreSQL array, regardless whether that is 1 or not. Maybe also add `to_json('[3:8]={3,4,5,6,7,8}'::int[])` becoming `[3,4,5,6,7,8]` to the examples. While we're at it, I would also like to see the documentation of `array_lower` and `array_upper` to be improved in https://www.postgresql.org/docs/current/functions-array.html. I was a bit surprised that when passing an empty array, they did return `NULL` instead of the "default" value `1`. Can you add that as an example to the two table rows, or just add a simple "(NULL if empty)" parenthetical to their descriptions? Alternatively, document it after the table: | In `array_lower` and `array_upper`, `NULL` is returned if the array dimension does not exist or is empty. kind regards, Andreas Bergmaier