Generate 2d array with given values

Question

I'm trying to turn a 1d jsonb array

[1, 2]

into 2d array where its elements are repeated 3 times (the result can be in jsonb)

[[1, 1, 1],
 [2, 2, 2]]

My attempt doesn't work

select array(select array_fill(a::text::integer, array[3])) 
  from jsonb_array_elements('[1,2]'::jsonb) as a;

ERROR:  could not find array type for data type integer[]

Maybe it would work in later PG version, but I'm restricted to PG 9.4.8

What are the other ways?

it does work in 9.6 if you use array_agg() instead of array() — Jasen
– Jasen, Commented Jan 10, 2017 at 23:23

Community · Accepted Answer · 2017-05-23 12:01:51Z

4

First of all, you need to replace array() with array_agg(), then you'll have what you expect, starting with Postgres 9.5.

That being said, your issue is with array_agg() not being able to aggregates arrays prior to 9.5.

Then are multiple existing answers for you, but basically you'll need to create a new aggregate function array_agg_mul:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat,
    STYPE     = anyarray,
    INITCOND  = '{}'
);

then running the following query should work:

SELECT
    array_agg_mult(array[array_fill(a::text::integer, array[3])])
FROM jsonb_array_elements('[1,2]'::jsonb) as a;

Then you should get:

  array_agg_mult
-------------------
 {{1,1,1},{2,2,2}}

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jan 11, 2017 at 8:39

fpietka

1,0411 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate 2d array with given values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related