Select query in postgresql to avoid null values of jsonb column

Question

I am using JPA to get data from Postgres Database by querying a table. One of the column of my table is jsonb. And a snippet of jsonb column from that table looks like that:

{"name": "Robert", "surname": "Lewandowski", "app_number": "46323875"}
{"name": "Robert", "surname": null, "app_number": "46323876"}
{"name": "Franck", "surname": "Ribery", "app_number": "46323877"}

I would like to implement a filtering and exclude null values in a result. In order to do that I am querying the table by using this piece of a code:

  @Query(value = "SELECT * FROM request_data rd\n" +
        "         WHERE(rd.params ->> 'name' = NULLIF(?1,'') is NULL or (rd.params ->> 'name' = ?1 and rd.params ->> 'name' != 'null'))\n" +
        "         and (rd.params ->> 'surname' = NULLIF(?2,'') is NULL or (rd.params ->> 'surname' = ?2 and rd.params ->> 'surname' != 'null'))\n" +
        "         and (rd.params ->> 'app_number' = NULLIF(?3,'') is NULL or (rd.params ->> 'app_number' = ?3 and rd.params ->> 'app_number' != 'null')) order by created_at DESC", nativeQuery = true)
List<RequestData> findTest(String name, String surname, String appNumber);

For example, I would like to get all entries by surname = "Lewandowski", and I am expecting the following result:

{"name": "Robert", "surname": "Lewandowski", "app_number": "46323875"}

But instead of it I got two rows including surname = null

{"name": "Robert", "surname": "Lewandowski", "app_number": "46323875"}
{"name": "Robert", "surname": null, "app_number": "46323876"}

I have tried a number of differents queries and syntaxes but, so far, I can not rid of null values in my result.

404 · Accepted Answer · 2018-06-08 16:32:49Z

1

Probably something like this would work:

AND (rd.params->>'surname') IS NOT DISTINCT FROM NULLIF(?2, '')

If the surname is null, "Lewandowski" will be distinct from it so will not be returned. If the requested surname is '' or NULL, the null surname will be returned, which I believe is what you want. And finally, if the surname is "Lewandowski", and the requested surname is the same, it will not be distinct and so return that result.

That check is all you need for the whole surname check. The rd.params ->> 'surname' != 'null' check seems wrong to me because that's an actual text value of "null", and how do you know no one has a surname called "null"? That's not the same as checking for null.

You can apply this same method to the other fields as well.

answered Jun 8, 2018 at 16:32

404

8,7622 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iurii Kvashuk Over a year ago

Thank you very much. So I put this row for the surname parameter and it works! - (rd.params ->> 'surname' = NULLIF(?2,'') is NOT NULL) and ((rd.params ->> 'surname') IS NOT DISTINCT FROM NULLIF(?2, ''))

Collectives™ on Stack Overflow

Select query in postgresql to avoid null values of jsonb column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related