CODEWITHSUNDEEP
javapostgresqlspring-bootjpa
Onkar Kore
Onkar Kore

Reputation: 75

Get nativeQuery with table join result list into JSONObject

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', '[email protected]');
insert into contacts values (2, 1, 'PHONE', '123');
insert into contacts values (3, 2, 'EMAIL', '[email protected]');
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": "[email protected]"
            },
            {
                "key": "PHONE",
                "value": "123"
            }
        ]
    },
    {
        "id": 2,
        "name": "B",
        "contacts": [{
                "key": "EMAIL",
                "value": "[email protected]"
            },
            {
                "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.

Upvotes: 1

Views: 2130

Answers (1)

S-Man
S-Man

Reputation: 23676

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.

Upvotes: 1

Related Questions