Re: generating json without nulls

On Thu, May 7, 2015 at 8:29 AM, Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)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.