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, Jan 10 '17 at 23:23

score 4 · Accepted Answer · edited May 23 '17 at 12:01

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}}

Generate 2d array with given values

1 Answers1