Convert SQL ResultSet to json

Question

I am using postgresql for my web application. I am new to this Postgresql-json. I just want to get the result of the select query as a 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 request:

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

result: bob

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

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

can any body tell me how to get the expected structure of the results from the database.

Answer 1

will be much simpler:

A is a regular postgresSQL database and response conversion to json.

• A1. store regular SQL database (and without postgres sql json)
• A2. get SQL resultSet (select query)

• A3. convert results to json using 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, which is the json source base

• B1. Store data in mongoDB as json
• IN 2. mongoDB request will return json resultset

Separation of decision A compared to decision B

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

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

Answer 2

You can reproduce the path that you follow as a string constant, provided that the path is corrected.

 select '{"person":{"name":{"lastname":"' || (info->'person'->'name'->>'lastname') || '"}}}'as val from sample; 

Answer 3

 SELECT json_build_object("person", json_build_object("name", json_build_object("lastname", (info->'person'->'name'->>'lastname')))) AS val FROM sample;