unnormalised data set to a nested document

Question

I'm using Derby embedded database and javax.json. To my knowledge I can't concatenate a result with derby. i.e:

ID | NAME | ROLE
----------------
1  | me   | admin, user
3  | root | root, user

I need to go from normalised database data to a Json document. I can't see how to do this other than to use an intermediate map and a two step SQL query. Links to how others have achieved this would be a brilliant help.

ID | NAME | ROLE
----------------
1  | me   | admin
1  | me   | user
3  | root | user
3  | root | root

{
   users : [
      {
          "user_id":1,
          "user_name":"me",
          "roles":[
              "admin",
               "user"
           ]
       },
       ...ect.

    ]
}

A user needs to be able to have multiple roles as I am using realms.

*"I can't see how to do this other than to use an intermediate map and a two step SQL query."* - Neither can I, if you are committed to just using `javax.json`. Are you specifically looking for some `javax.json` "magic", or are you fishing for other possible solutions, e.g., as discussed [here](http://stackoverflow.com/q/6514876/2144390)? — Gord Thompson, Aug 07 '16 at 21:32
I just find javax.json the most useful library. I often populate a JsonArrayBuilder looping through a result set and then build the finished JSON after. I got stuck here as you cannot iterate a JsonBuilder object, only a JsonObject. — Christian Lacdael, Aug 08 '16 at 04:55

score 0 · Answer 1 · answered Aug 08 '16 at 05:08

This must surely be a common problem, but I can't word my searches to find more information.

I used SQL in the end, which obviously won't be appropriate in every situation.

"SELECT users.id, users.username, "
    + "SUM (CASE ROLE WHEN 'user-role' THEN 1 ELSE 0 END) , "
    + "SUM (CASE ROLE WHEN 'admin-role' THEN 1 ELSE 0 END) , "
    + "SUM (CASE ROLE WHEN 'root-role' THEN 1 ELSE 0 END) " 
    + "FROM users INNER JOIN user_roles ON user_roles.user_id = users.id "
    + "INNER JOIN roles ON user_roles.role_id = roles.id"
    + "GROUP BY users.id, users.username

Using a sum for each of the values ROLE can take, a returned row: (anId, aName, 1, 0, 1) would tell me aName was root and a user.

Hopefully list aggregation will be added to the SQL standard soon.

Gord Thompson · Answer 2 · 2016-08-08T20:28:30.720

For the record, and for possible benefit of future readers, with the following tables

"users"

id  username
--  --------
 1  me
 3  root
 4  gord


"roles"

id  ROLE
--  ----------
 1  admin-role
 2  user-role
 3  root-role


"user_roles"

user_id  role_id
-------  -------
      1        1
      1        2
      3        2
      3        3

the Java code

try (
        Statement st = conn.createStatement();
        ResultSet rsUser = st.executeQuery(
                "SELECT DISTINCT id, username FROM users");
        PreparedStatement ps = conn.prepareStatement(
                "SELECT ROLE " + 
                "FROM user_roles INNER JOIN roles " + 
                    "ON roles.id = user_roles.role_id " + 
                "WHERE user_id = ?")) {
    JsonObjectBuilder jobRoot = Json.createObjectBuilder();
    JsonArrayBuilder jabUsers = Json.createArrayBuilder();
    while (rsUser.next()) {
        JsonObjectBuilder jobUser = Json.createObjectBuilder();
        jobUser.add("user_id", rsUser.getInt("id"));
        jobUser.add("user_name", rsUser.getString("username"));
        ps.setInt(1, rsUser.getInt("id"));
        try (ResultSet rsRole = ps.executeQuery()) {
            if (rsRole.next()) {
                JsonArrayBuilder jabRoles = Json.createArrayBuilder();
                do {
                    jabRoles.add(rsRole.getString("ROLE"));
                } while (rsRole.next());
                jobUser.add("roles", jabRoles.build());
            }
        }
        jabUsers.add(jobUser);
    }
    jobRoot.add("users", jabUsers.build());
    JsonWriter jw = Json.createWriter(
            new FileOutputStream("C:/Users/Gord/Desktop/json.txt"));
    jw.writeObject(jobRoot.build());
    jw.close();
}

produces

{
   "users":[
      {
         "user_id":1,
         "user_name":"me",
         "roles":[
            "admin-role",
            "user-role"
         ]
      },
      {
         "user_id":3,
         "user_name":"root",
         "roles":[
            "user-role",
            "root-role"
         ]
      },
      {
         "user_id":4,
         "user_name":"gord"
      }
   ]
}

unnormalised data set to a nested document

2 Answers2