How to convert database result set as JSON format in java?

Question

I want to convert the result set from the db into json format, the problem is when the result set returned as multiple rows for the same id with some duplicates data.

DB example:

===========================================================
USER_ID         NAME          PHONE_NUMBER         CITY
===========================================================
1               JACK          079999999999        New York
1               JACK          078888888888        Las Vegas

I want to make my json body looks like:

{ "USER_ID": 1,"NAME": JACK,"PHONE_NUMBER":[ 079999999999, 078888888888 ], "CITY": [ New York, Las Vegas ]}

this is my code:

                Statement stmt = con.createStatement();
                // Execute the SQL Query. Store results in ResultSet
                ResultSet rs = stmt.executeQuery(Query);
                System.err.println("Executing the query please wait ...");
                ResultSetMetaData rsmd=null;
                rsmd=(ResultSetMetaData) rs.getMetaData();
                int columnsCount=rsmd.getColumnCount();


                for (int j = 1; j <= columnsCount; j++) {
                    System.err.print(rsmd.getColumnName(j) + " || ");
                }


            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                int total_rows = rs.getMetaData().getColumnCount();
                JSONObject obj = new JSONObject();

                for (int i = 0; i < total_rows; i++) {
                    String columnName = rs.getMetaData().getColumnLabel(i + 1).toLowerCase();
                    Object columnValue = rs.getObject(i + 1);

                    // if value in DB is null, then we set it to default value
                    if (columnValue == null){
                        columnValue = "null";
                    }

                    if (obj.has(columnName)){
                        columnName += "1";
                    }
                    obj.put(columnName, columnValue);

                }
                jsonArray.put(obj);

                System.out.print("\n");
                System.out.print("\n");

                String gsonBody = gson.toJson(jsonArray);

                System.err.println(gsonBody);
            }
            return jsonArray;

How can i make this general for all possible scenarios with different result set.

Answer 1

which RDBMS are you using? if Postgres/db2 is your database than you could return the resultset directly as JSON.

Answer 2

Using SQL/JSON

In many modern RDBMS, you don't have to implement any Java logic for that. The standard SQL way to do this is by using the SQL/JSON extensions. In your specific case, you could write

SELECT 
  user_id, 
  name,
  JSON_ARRAYAGG(phone_number) AS phone_number,
  JSON_ARRAYAGG(city) AS city
FROM t
GROUP BY user_id, name

Other dialects have different syntax for the same thing

Doing this in Java

Since you're asking how to do this specifically in Java, you could use jOOQ, which has extensive SQL/JSON support and would allow you to write the above query in a type safe way:

String json =
ctx.select(
        T.USER_ID,
        T.NAME,
        jsonArrayAgg(T.PHONE_NUMBER).as(T.PHONE_NUMBER),
        jsonArrayAgg(T.CITY).as(T.CITY))
   .from(T)
   .groupBy(T.USER_ID, T.NAME)
   .fetch()
   .formatJSON();

The benefit would be that this would take care of emulating the JSON array aggregation for your specific dialect and version, in case you have to work around some caveats.

(Disclaimer: I work for the company behind jOOQ)

A side note on normalisation

In the long term, you should think about normalising your data. You shouldn't have duplicate entries for USER_ID and NAME in this table, as it would allow for data anomalies (e.g. different NAME for the same USER_ID)