Get nativeQuery with table join result list into JSONObject

Question

I am working on a spring boot project with postgress. I am facing an issue while parsing jpa native query (with table join) result list into JSONObject

I have two tables

create table person
(
  id           serial primary key,
  name        varchar(50)
);

create table contacts
(
  id serial primary key,
  person_id int,
  key   varchar(50),
  value varchar(100),
  foreign key (person_id) references person (id)
);

Sample data in the table

insert into person values (1, 'A');
insert into person values (2, 'B');
insert into contacts values (1, 1, 'EMAIL', 'a@b.com');
insert into contacts values (2, 1, 'PHONE', '123');
insert into contacts values (3, 2, 'EMAIL', 'b@b.com');
insert into contacts values (4, 2, 'PHONE', '456');

Spring boot code to fetch the data by using native query.

String query = "select p.id, p.name, c.key, c.value from person p LEFT JOIN contacts c on p.id = c.person_id";
Query nativeQuery = entityManager.createNativeQuery(query);
List<Object[]> resultList = nativeQuery.getResultList();

I would like to get the output as a list of JSONObject

[{
        "id": 1,
        "name": "A",
        "contacts": [{
                "key": "EMAIL",
                "value": "a@b.com"
            },
            {
                "key": "PHONE",
                "value": "123"
            }
        ]
    },
    {
        "id": 2,
        "name": "B",
        "contacts": [{
                "key": "EMAIL",
                "value": "b@b.com"
            },
            {
                "key": "PHONE",
                "value": "456"
            }
        ]
    }
]

OR the result should be directly into POJO

List<Person> resultList = nativeQuery.getResultList();

in this case, I have a person class and contacts class with @OneToMany annotation.

You don't need the indirection of Java objects. PostgreSQL has JSON functions to create JSON output of your query. https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql — Simon Martinelli, Aug 06 '19 at 14:41
The whole point about ORMs is to work with objects, so outputting JSON at that layer is a little bit weird. You should strive to get a collection of `Person`...and that's doable in many ways when using JPA. — x80486, Aug 06 '19 at 14:48
I think it's better to refer earlier answer: https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection — Prashant Jha, Aug 06 '19 at 15:12

S-Man · Accepted Answer · 2019-08-06T15:01:56.007

demo:db<>fiddle (Native Query)

SELECT 
    json_agg(json_person)
FROM
    person p
JOIN (
    SELECT
        person_id,
        json_agg(json_contacts) AS contacts
    FROM
        contacts,
        json_build_object('key', key, 'value', value) as json_contacts
    GROUP BY person_id
) c
ON c.person_id = p.id,
json_build_object('id', p.id, 'name', p.name, 'contacts', c.contacts) AS json_person

docs: Postgres JSON function

You have to create an JSON object using json_build_object() and aggregate these results into an array using json_agg(). Twice.

Disclaimer: As already stated in the comments: In this case a simple JOIN of both tables would be ok to transfer the logic into the Java backend. Because you are using the JPA you just have the data within your Entity manager, I guess. So using a Java tool (e.g. gson) for generating the JSON objects will be the better choice instead of calling the database. But this really depneds on your use case and setup, of course.

Get nativeQuery with table join result list into JSONObject

1 Answers1