Re: Calling json_* functions with JSONB data

On Mon, May 23, 2016 at 4:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Peter van Hardenberg <pvh(at)pvh(dot)ca> writes:
> > Great question, Marko. If you can point me towards an example I'll take a
> > look, but I'll proceed with the current understanding and suggestions and
> > see what people have to say.
>
> I believe Marko's just complaining about the case for unknown-type
> arguments, for example:
>
> regression=# select json_array_length('[1,2,3]');
> json_array_length
> -------------------
> 3
> (1 row)
>
> The parser has no trouble resolving this because there is only one
> json_array_length(); but if there were two, it would fail to make a
> determination of which one you meant.
>
> AFAICS the only way to fix that would be to introduce some preference
> between the two types. For example, we could move both 'json' and 'jsonb'
> into their own typcategory ('J' is unused...) and then mark 'jsonb' as
> the preferred type in that category. This would require a fair amount of
> experimentation to determine if it upsets any cases that work conveniently
> today; but right offhand I don't see any fatal problems with such an idea.
>
> regards, tom lane
>

​
​
I
​ guess the relevant point in the documentation is the parenthetical
sentence:

​
(The processing functions consider the last value as the operative one.)

http://www.postgresql.org/docs/9.5/static/datatype-json.html
​
​Which normalizes the behaviors of jsonb and json as they pass through one
of these functions. Though only the multi-key is noted which means
white-space (immaterial) and key-order (potentially material) behaviors
differ; though the later coming through the function unscathed is not
something that user's should be relying upon. Specifically I'm thinking of
the behavior that "json_each(...)" would exhibit.

David J.