Parse Array_agg on all columns

Question

I use PostgreSQL 10.6 on Ubuntu 10.6-1.pgdg18.04+1 with Java-8. I have two tables:

Table test

 Column |         Type         | 
--------+----------------------+
 id     | character varying(5) |

Table test2

 Column |         Type          | 
--------+-----------------------+---
 c1     | character varying(5)  |   
 c2     | character varying(10) |
 c3     | character varying(10) |
 c4     | character varying(10) |

I am trying to fetch result from two tables in one go by JOIN.

This is my query

SELECT t1.*, Array_agg(t2.*) AS ent
FROM test AS t1 LEFT JOIN
     test2 AS t2
     ON t1.id = t2.c1 GROUP BY t1.id;

gives result

 id |            ent            
----+---------------------------
 a  | {"(a,e,f,g)","(a,b,c,d)"}

After getting values ent column

Array arr = rs.getArray("ent");

Object objects = arr.getArray();

object contains

objects = ["(a,e,f,g)","(a,b,c,d)"]

Now how to parse the result and get values as individual values since it is Object type ?

Casting it to ResultSet[] gives cannot be cast to [Ljava.sql.ResultSet

Dimitar Spasovski · Accepted Answer · 2018-12-16 19:13:12Z

3

As far as I can see from your question, the getArray method returns an Array of Strings. Instead of casting the Array to Object you can cast the results to an Array of Strings.

Example code for casting:

String[] arr = (String[])rs.getArray("ent");

Once you have an array of Strings, you can use String functions in order to parse and extract the values. For example, if the the following code if executed on your example input:

String[] arr = (String[])rs.getArray("ent");
    for(String row : arr) {
        String[] letters = row.replace("(","").replace(")","").split(",");
        for(String letter : letters) {
            System.out.println(letter);
        }
    }

The following result should be printed:

a
e
f
g
a
b
c
d

answered Dec 16, 2018 at 19:13

Dimitar Spasovski

2,18210 gold badges29 silver badges48 bronze badges

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

Comments

Nikolas · Accepted Answer · 2018-12-16 19:23:48Z

3

After casting the array of objects to String[], there is a fairly and straightforward approach using java-stream:

String[] arr = (String[]) rs.getArray("ent");

Arrays.stream(arr)                                             // array to Stream
      .map(string -> string.replaceAll("[()]", "").split(",")) // remove brackets and split
      .flatMap(Arrays::stream)                                 // flatten the array 
      .forEach(System.out::println);                           // print the letters out

If you want the newly parsed items to the new array String[], use the Stream::toArray with the generator to get a correct type of array:

String[] array = Arrays.stream(arr)
                       .map(string -> string.replaceAll("[()]", "").split(","))
                       .flatMap(Arrays::stream)
                       .toArray(String[]::new);

System.out.println(Arrays.asList(array));       // [a, e, f, g, a, b, c, d]

answered Dec 16, 2018 at 19:23

Nikolas

45k19 gold badges132 silver badges201 bronze badges

2 Comments

techcomp Over a year ago

So there is no way other than string manipulation and then parsing?

techcomp Over a year ago

Is there some way other than string manipulation and then parsing? Is it possible to convert it back to ResultSet so that values can be accessed by column name. I am trying to avoid this approach because table rows can contain special characters so more sophisticated string functions will be required.

HPH · Accepted Answer · 2018-12-17 20:22:54Z

1

To collect the results as String[][] for each row in the result set, you should perform the following :

Arrays.stream((String[])rs.getArray("ent"))
    .map(element -> element.replaceAll("[()]", ""))
    .map(element -> element.split(","))
    .toArray(String[][]::new)

answered Dec 17, 2018 at 20:22

HPH

3982 silver badges11 bronze badges

Comments

Panagiotis Nicolaou · Accepted Answer · 2024-01-26 06:36:15Z

0

Use PgArray.getArray() to avoid any string manipulation:

String[] entAsArray = (String[]) rs.getArray("ent").getArray();
List<String> entAsList = Arrays.stream(entAsArray).collect(Collectors.toList());

answered Jan 26, 2024 at 6:36

Panagiotis Nicolaou

1,5711 gold badge9 silver badges9 bronze badges

Collectives™ on Stack Overflow

Parse Array_agg on all columns

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related