Transform SQL ResultSet to json

Question

I am using postgresql for my webapplication. I am new to this Postgresql-json. I Just want to get the select query result in the form of json structure. Here are my details:

create table sample(id serial, info jsonb);
insert into sample("info") values('{"person": {"phone": 9804484234,"name":{"firstname":"Alice", "lastname":"bob"}, "empId": "E067", "age":25}');

select query:

select "info"->'person'->>'lastname' from sample;

result: bob

but I want to get the above result along with the json nodes like below:

result: {"person":
          {"name":
            {"lastname":"bob"}
          }
        }

could any body tell me how to get my expected result structure from database.

Answer 1

will be much more simple to have:

A- a normal postgresSQL database and transform response to json.

• A1. store a normal SQL DB (and no postgres sql json)
• A2. retrieve a SQL resultSet (select query)

• A3. convert resultset into json with this code

  public class SOF_36861985 {
  
             public static JSONArray toJson(ResultSet res) throws Exception {
                  JSONArray array = new JSONArray();
                  while (res.next()) {
                      int size = res.getMetaData().getColumnCount();
                      JSONObject obj = new JSONObject();
                      for (int i = 0; i < size; i++) {
                          obj.put( res
                                  .getMetaData()
                                  .getColumnLabel(i + 1)
                                  .toLowerCase(), 
                                  res.getObject(i + 1));
                          array.put(obj);
                      }
                  }
                  return array;
              }
  
      }
  

or

B. Use mongoDB that is json native database

• B1. Store datas in mongoDB as json
• B2. query mongoDB will return json resultset

Comparation of Solution A versus Solution B

Solution A : sql + does not force you to have a new DB, you will continue with postgressql - will make a convertion from ResultSet to Json - will have static schema in SQL database (no dynamic schema as in nosql)

Solution B: mongo - makes change of DB, that is production dependant... and has impact on infrastructure.... + is json native DB + is probably a new DB for you, you will have a learning time to master it (will take more time to setup, install, dev...)