How to display a row without showing its null columns in PostgreSQL?

Question

I want to show all the rows in my table with all the columns except those columns that are null.

-- SELECT all users
SELECT * FROM users
ORDER BY user_id ASC;

-- SELECT a user
SELECT * FROM users
WHERE user_id = $1;

Currently my API's GET request returns something like this with the above queries:

{
   "user_id": 10,
   "name": "Bruce Wayne",
   "username": "Batman",
   "email": "bat@cave.com",
   "phone": null,
   "website": null
}

Is there any way I can display it like this so that the null columns aren't shown?

{
   "user_id": 10,
   "name": "Bruce Wayne",
   "username": "Batman",
   "email": "bat@cave.com"
}

You should handle this from JavaScript. See [this SO question and answer](https://stackoverflow.com/questions/23774231/how-do-i-remove-all-null-and-empty-string-values-from-an-object) for how to remove null/empty keys from a JSON object. — Tim Biegeleisen, Mar 10 '22 at 05:21
You can use this trick https://stackoverflow.com/a/41116529/6759368 — Pooya, Mar 10 '22 at 06:15
@TimBiegeleisen Thanks for the help. Your comment led me to another page and I was able to solve my problem by following this answer: https://stackoverflow.com/a/57625661/16590740 — Nima, Mar 10 '22 at 07:52
You can make Postgres return such a JSON value if you want: `select jsonb_strip_nulls(to_jsonb(users)) from users where ...` I don't know Node.js, so I can't tell how it would handle that though. — , Mar 10 '22 at 09:11

score 0 · Answer 1 · answered Mar 12 '22 at 16:39

I understand that you are using serialized (or deserialized) JSON and objects in your code. More serialized modules have special parameters that as ignore nulls and etc.

If you generate this JSON format data on the DB, in the inside SQL codes, then you can use Postgres jsonb_strip_nulls(JSONB) function. This function automatically removes all null values keys in the JSONB recursively and returns the JSONB type.

How to display a row without showing its null columns in PostgreSQL?

1 Answers1