Can't get nulls values using java query to Postgresql

Question

Query in repository:

@Query("SELECT r.type AS roomType, r.name AS roomName, r.number AS roomNumber, MIN(mm.doubleValue) AS startValue 
         FROM Measurement AS mm 
         LEFT JOIN mm.meter AS m 
         LEFT JOIN m.room AS r ...)
    methodNameRepository(String name, ...){}

I get all objects with all non-null fields:

[
  {
    "roomType": "FLAT",
    "roomNumber": 1,
    "roomName": "Flat",
    "startValue": 20,
    "endDate": "2022-07-01T20:59:00",
    "endValue": 23
  },
  ...
]

I must get objects with some null fields, like this:

[
  {
    "roomType": "FLAT",
    "roomNumber": 1,
    "roomName": "Flat",
    "startValue": null,
    "endDate": "2022-07-05T20:59:00",
    "endValue": 28
  },
  {
    "roomType": "FLAT",
    "roomNumber": 1,
    "roomName": "Flat",
    "startValue": null,
    "endDate": "2022-07-12T20:59:00",
    "endValue": null
  },
  ...
]

This question should include more details and clarify the problem. — Roman C, Jul 12 '22 at 15:06
If `startValue` is a MIN it will by design ignore null values, what to do with that would depend on how you're doing aggregation. There is no `endValue` in your query. Without the full query and some sample data to play around with it's hard to say anything beyond that. — Deltharis, Jul 12 '22 at 15:16
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Naga Budigam, Jul 12 '22 at 16:36

score 0 · Answer 1 · answered Jul 12 '22 at 15:40

0

The rows containing null value in the doubleValue column are not found because the MIN function does not take the null value into account.

possible resolution for this problem : How can I include null values in a MIN or MAX?

answered Jul 12 '22 at 15:40

Massnissa BELAID

31
2

Can't get nulls values using java query to Postgresql

1 Answers1