CODEWITHSUNDEEP
sqljsonpostgresql-9.3postgresql-9.5
M.S.Naidu
M.S.Naidu

Reputation: 2289

Transform SQL ResultSet to json

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.

Upvotes: 5

Views: 799

Answers (3)

user827329
user827329

Reputation: 

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

Upvotes: 0

Serg
Serg

Reputation: 22811

You can reproduce the path you are following as a string constant, provided the path is fixed.

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

Upvotes: 1

jeorfevre
jeorfevre

Reputation: 2316

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...)

Upvotes: 1

Related Questions