0

I need to split text elements in an array and combine the elements (array_agg) by index into different rows

E.g., input is

'{cat$ball$x... , dog$bat$y...}'::text[]

I need to split each element by '$' and the desired output is:

{cat,dog} - row 1
{ball,bat} - row 2
{x,y} - row 3
...

Sorry for not being clear the first time. I have edited my question. I tried similar options but unable to figure out how to get it with multiple text elements separated with '$' sysmbol

LJU
  • 13
  • 2

2 Answers2

1

Exactly two parts per array element (original question)

Use unnest(), split_part() and array_agg():

SELECT array_agg(split_part(t, '$', 1)) AS col1
     , array_agg(split_part(t, '$', 2)) AS col2
FROM   unnest('{cat$ball, dog$bat}'::text[]) t;

Related:

General solution (updated question)

For any number of arrays with any number of elements containing any number of parts.

Demo for a table tbl:

CREATE TABLE tbl (tbl_id int PRIMARY KEY, arr text[]);

INSERT INTO tbl VALUES
  (1, '{cat1$ball1, dog2$bat2}')  -- 2 parts per array element, 2 elements
, (2, '{cat$ball$x, dog$bat$y}')  -- 3 parts ...
, (3, '{a1$b1$c1$d1, a2$b2$c2$d2, a3$b3$c3$d3}');  -- 4 parts, 3 elements

Query:

SELECT tbl_id, idx, array_agg(elem ORDER BY ord) AS pivoted_array
FROM   tbl t
     , unnest(t.arr) WITH ORDINALITY a1(string, ord)
     , unnest(string_to_array(a1.string, '$')) WITH ORDINALITY a2(elem, idx)
GROUP  BY tbl_id, idx
ORDER  BY tbl_id, idx;

We are looking at two (nested) LATERAL joins here. LATERAL requires Postgres 9.3. Details:

WITH ORDINALITY for the the first unnest() is up for debate. A simpler query normally works, too. It's just not guaranteed to work according to SQL standards:

SELECT tbl_id, idx, array_agg(elem) AS pivoted_array
FROM   tbl t
     , unnest(t.arr) string
     , unnest(string_to_array(string, '$')) WITH ORDINALITY a2(elem, idx)
GROUP  BY tbl_id, idx
ORDER  BY tbl_id, idx;

Details:

WITH ORDINALITY requires Postgres 9.4 or later. The same back-patched to Postgres 9.3:

SELECT tbl_id, idx, array_agg(arr2[idx]) AS pivoted_array
FROM   tbl t
     , LATERAL (
         SELECT string_to_array(string, '$') AS arr2  -- convert string to array
         FROM   unnest(t.arr) string  -- unnest org. array
         ) x
     , generate_subscripts(arr2, 1) AS idx  -- unnest 2nd array with ord. numbers
GROUP  BY tbl_id, idx
ORDER  BY tbl_id, idx;

Each query returns:

 tbl_id | idx | pivoted_array
--------+-----+---------------
      1 |   1 | {cat,dog}
      1 |   2 | {bat,ball}
      1 |   3 | {y,x}
      2 |   1 | {cat2,dog2}
      2 |   2 | {ball2,bat2}
      3 |   1 | {a3,a1,a2}
      3 |   2 | {b1,b2,b3}
      3 |   3 | {c2,c1,c3}
      3 |   4 | {d2,d3,d1}

SQL Fiddle (still stuck on pg 9.3).

The only requirement for these queries is that the number of parts in elements of the same array is constant. We could even make it work for a varying number of parts using crosstab() with two parameters to fill in NULL values for missing parts, but that's beyond the scope of this question:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for your reply!!. Sorry for not being clear the first time. I tried similar things and now trying to solve when there are varying number of strings separated with '$' sign. Also, the number of text separated by '$' in each array element will always be the same – LJU May 27 '16 at 17:19
  • Thank you!! (For the solution and the references). Good Learning!! – LJU May 30 '16 at 18:43
0

A bit messy but you could unnest the array, use regex to separate the text and then aggregate back up again:

with a as (select unnest('{cat$ball, dog$bat}'::_text) some_text),
b as (select regexp_matches(a.some_text, '(^[a-z]*)\$([a-z]*$)') animal_object from a)
select array_agg(animal_object[1]) animal, array_agg(animal_object[2]) a_object
from b

If you're processing multiple records at once you may want to use something like a row number before the unnest so that you have a group by to aggregate back to an array in your final select statement.

kiwiray
  • 91
  • 1
  • 4