Re: generating json without nulls

Thanks.
Using the CASE ... THEN ... ELSE ... END approach seems to work best for me.
Not especially elegant, but it does the job.

Tim

On Thu, May 7, 2015 at 8:29 AM, Tim Dudgeon <tdudgeon.ml@gmail.com> wrote:

That's not going to work. I want the row, I just don't want the values that are null.

Only thing that comes to mind:

1. Use the conversion function to get the json structure with nulls.

2. Use an explode function to convert the json into a table structure with (key, value) columns.

3. Filter that table where value is not null.

4. Convert the remaining entries into arrays

5. Pass the two arrays back into the json_object(keys text[], values text[])

You could dynamically build up a literal string array but the syntax challenges scare me:

json_object('{' ||

CASE WHEN col1 IS NULL THEN '' ELSE '"col1",' || val1 || '"' END ||

CASE WHEN col2 IS NULL THEN '' ELSE '"col2",' || val2 || '"' END ||

'}'::text[])

David J.