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

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": "[email protected]",
   "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": "[email protected]"
}
sqlnode.jspostgresqlexpressnode-postgres


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ramin Faracov

Related Questions