Excluding nulls from row_to_json() result in PostgreSQL

Question

I want to convert the rows of a record set to JSON, but not include any null entries that are just going to end up being undefined in JavaScript anyway. For example, suppose I have the table testdata with entries

id | prop1 (integer) | prop2 (text)
-------------------------------------
 1 |               42 | 'Answer'
 2 |             NULL | 'No prop one'
 3 |                0 | NULL

and then execute

SELECT row_to_json(testdata) FROM testdata

What I get is:

{"id":"1","prop1":"42","prop2":"Answer"}
{"id":"2","prop1":null,"prop2":"No prop one"}
{"id":"3","prop1":"0","prop2":null}

But instead, what I want is:

{"id":"1","prop1":"42","prop2":"Answer"}
{"id":"2","prop2":"No prop one"}
{"id":"3","prop1":"0"}

Is this possible? According to the JSON functions documentation for PostgreSQL 9.3, there's only one extra option or parameter for row_to_json, but setting pretty_bool=true doesn't remove the nulls, so it seems as if the answer may be no. But this also seems as if it's a very obvious and useful function, so I'm hoping that somebody else has found something I've missed.

My end goal is to retrieve the records in JavaScript with a GET call to a PHP page. Am I better off building the JSON in PHP from a more standard recordset, instead of using PostgreSQL's JSON routines?

but not include any null entries that are just going to end up being undefined in JavaScript anyway The property will be null not undefined. It will be undefined if you remove it from the object as you want to do. — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 15, 2014 at 20:15
I meant that I need them to be undefined, so they will end up being undefined in my particular JavaScript application -- it's just a question of where along the chain from PostgreSQL through PHP to JS that I manage to make that happen. Of course, that doesn't affect my question in any way... — Michael
– Michael, Commented Apr 15, 2014 at 20:26
Are you sure there is a gain in having the property undefined in instead of null? I don't see the point. Ask it to the JS experts. — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 15, 2014 at 22:15
I suppose you're right, there's probably not much difference... I hadn't thought about it much, because my goal is to not have the values there at all. My question here isn't about JavaScript, it's about PostgreSQL and how to generate JSON such that I don't have to send the missing values to the browser in the first place. — Michael
– Michael, Commented Apr 15, 2014 at 22:25

murison · Accepted Answer · 2016-05-20 08:56:59Z

16

Postgres 9.5 introduces json_strip_nulls function, that seems to do exactly what you want.

answered May 20, 2016 at 8:56

murison

4,0552 gold badges26 silver badges44 bronze badges

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

Comments

T.R. · Accepted Answer · 2014-10-03 09:49:48Z

4

Looks like future versions of the function may have an 'ignore nulls' option - https://commitfest.postgresql.org/action/patch_view?id=1496

answered Oct 3, 2014 at 9:49

T.R.

7,6426 gold badges32 silver badges34 bronze badges

2 Comments

David Waller Over a year ago

Sadly this patch was pulled from the code - see this discussion. Hopefully an alternative will make it in time for Postgres 9.5, though I can't see anything in the Postgres code at the moment.

Ross Bradbury Over a year ago

Postgresql 9.5 (currently still in development) has the json_strip_nulls function.

Collectives™ on Stack Overflow

Excluding nulls from row_to_json() result in PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related