Re: Bypassing NULL elements in row_to_json function

On Fri, Apr 8, 2016 at 8:53 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:

> On 08/04/2016 16:31, Michael Nolan wrote:
> > I'm looking at the possibility of using JSON as a data exchange format
> > with some apps running on both PCs and Macs. .
> >
> > The table I would be exporting has a lot of NULL values in it. Is
> > there any way to skip the NULL values in the row_to_json function and
> > include only the fields that are non-null?
>
> You could use a CTE to filter out the nulls (not tested - I haven't used
> JSON in PG (yet!)):
>
> with no_nulls as (
> select ... from my_table
> where whatever is not null
> )
> select row_to_json(....) from no_nulls;
>

​One of us is confused. I'm reading this as "I want a row_to_json" call to
generate objects with different keys depending on whether a given key would
have a null - in which case exclude the key.

I
​ think one would have to simply allow row_to_json to populate the keys
with null values and then post-process them away:

json_strip_nulls(row_to_json(...))​

http://www.postgresql.org/docs/current/static/functions-json.html

David J.