Ignore NULL columns from the result in postgres

Question

I'm really new to Postgres and was wondering if there is an easy way to do this. I have a table where I will only query a single row at a time. Every row is about a product and every column is basic nutritional info on the pack which looks like this:

click here for image

While querying a single row, I want to ignore all the columns that have null values and return the rest in a JSON format. I haven't found a good way to filter the null columns.

I found this is how you convert the data to JSON in Postgres and it works well but because every product (row) will have different nutritional info (column) so I don't how to implement this.

If there is no way to implement this then can I send everything to the frontend and filter it there? Will that affect the performance a lot? I have about 150 columns.

user330315 · Accepted Answer · 2021-08-18 07:45:34Z

3

You can convert the complete row to a JSON value without NULL values using jsonb_strip_nulls()

select jsonb_strip_nulls(to_jsonb(t))
from the_table t
where ...

to_jsonb(t) converts the complete row to a JSON value where the column name is the key. jsonb_strip_nulls() will then remove those keys with a NULL value.

answered Aug 18, 2021 at 7:45

user330315

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

1 Comment

Dev Over a year ago

This was exactly what I was looking for, can't thank you enough.

Collectives™ on Stack Overflow

Ignore NULL columns from the result in postgres

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