Re: JSON[B] arrays are second-class citizens

On 5/31/16 7:04 PM, Peter van Hardenberg wrote:
> The idea of converting a JSONB array to a PG array is appealing and
> would potentially be more general-purpose than adding a new unnest. I'm
> not sure how feasible either suggestion is.

The one part I think is missing right now is unnest allows you to
'stitch' or 'zip' multiple arrays together into a single recordset via
unnest(array1, array2, ...). Presumably that could be added to the json
equivalents.

> I will say that I think the current state of affairs is gratuitously
> verbose and expects users to memorize a substantial number of long
> function names to perform simple tasks.

+100. It's *much* easier to deal with JSON in other languages because
they have native support for the concept of a dictionary, so changing an
element is as simple as json['foo'][3] = 'new'. Trying to do that in
Postgres is horrible partly because of the need to remember some odd
operator, but moreso because it's ultimately still an operator. What we
need is a form of *addressing*. If you could directly access items in a
JSON doc with [] notation then a lot of the current functions could go
away, *especially* if the [] notation allowed things like a slice and a
list of values (ie: json['foo', 'bar', 'baz'] = '[42,{"my": "nice
object"},"with a random string"]'. Or = row(42, ...).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461