Postgres Pro
Downloads
Home   >   mailing lists

Re: generating json without nulls - Mailing list pgsql-sql

From David G. Johnston
Subject Re: generating json without nulls
Date
Msg-id CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com
Whole thread Raw
In response to Re: generating json without nulls  (Tim Dudgeon <tdudgeon.ml@gmail.com>)
Responses Re: generating json without nulls  (Tim Dudgeon <tdudgeon.ml@gmail.com>)
List pgsql-sql
Tree view
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.

pgsql-sql by date:

Previous
From: Tim Dudgeon
Date:
Subject: Re: generating json without nulls
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: generating json without nulls