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

On Tue, May 31, 2016 at 5:06 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, May 31, 2016 at 4:34 PM, David Fetter <david(at)fetter(dot)org> wrote:
>
>> Folks,
>>
>> While querying some JSONB blobs at work in preparation for a massive
>> rework of the data infrastructure, I ran into things that really
>> puzzled me, to wit:
>>
>> SELECT * FROM unnest('["a","b","c"]'::jsonb);
>> ERROR: function unnest(jsonb) does not exist
>>
>> SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
>> value
>> ───────
>> "a"
>> "b"
>> "c"
>> (3 rows)
>>
>>
> ​I'd be inclined to -1 such a proposal. TIMTOWTDI is not a principle that
> we endeavor to emulate.
>
> Having an overloaded form: <unnest(jsonb) : setof jsonb> is unappealing.
> While likely not that common the introduction of an ambiguity makes raises
> the bar considerably.
>
> That said we do seem to be lacking any easy way to take a json array and
> attempt to convert it directly into a PostgreSQL array. Just a conversion
> is not always going to succeed though the capability seems worthwhile if as
> yet unasked for. The each->convert->array_agg pattern works but is likely
> inefficient for homogeneous json array cases.
>
> David J.
>

If there is no list of people asking for that function, let me be the first.

In the mean time, I've resigned myself to carting this around from db to
db...

create function jsonb_array_to_text_array(jsonb_arr jsonb) returns text[]
language sql as $$
select array_agg(r) from jsonb_array_elements_text(jsonb_arr) r;
$$;