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

Answer 1

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