Postgres Pro
Downloads
Home   >   mailing lists

Thread: generating json without nulls

generating json without nulls

From
Tim Dudgeon
Date:
Hi All!
I'm using the postgres json functions to generate json for values in a 
table.
Something like this:

SELECT row_to_json(a_table) FROM a_table

But my data has lots of null values and that results in json attributes 
like this:

"colname":null

I want to exclude those values from the json and only include non-null 
values.
Any idea how to best go about this?

Tim

Re: generating json without nulls

From
Andreas Joseph Krogh
Date:
 På torsdag 07. mai 2015 kl. 12:56:42, skrev Tim Dudgeon <tdudgeon.ml@gmail.com>:
Hi All!
I'm using the postgres json functions to generate json for values in a
table.
Something like this:

SELECT row_to_json(a_table) FROM a_table

But my data has lots of null values and that results in json attributes
like this:

"colname":null

I want to exclude those values from the json and only include non-null
values.
Any idea how to best go about this?

Tim
 
WHERE colname IS NOT NULL ?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: generating json without nulls

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

Tim

On 07/05/2015 16:16, Andreas Joseph Krogh wrote:
 På torsdag 07. mai 2015 kl. 12:56:42, skrev Tim Dudgeon <tdudgeon.ml@gmail.com>:
Hi All!
I'm using the postgres json functions to generate json for values in a
table.
Something like this:

SELECT row_to_json(a_table) FROM a_table

But my data has lots of null values and that results in json attributes
like this:

"colname":null

I want to exclude those values from the json and only include non-null
values.
Any idea how to best go about this?

Tim
 
WHERE colname IS NOT NULL ?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Attachment

Re: generating json without nulls

From
"David G. Johnston"
Date:
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.

Re: generating json without nulls

From
Andreas Joseph Krogh
Date:
På torsdag 07. mai 2015 kl. 17:29:04, skrev Tim Dudgeon <tdudgeon.ml@gmail.com>:
That's not going to work. I want the row, I just don't want the values that are null.
 
I'm sorry, forgot to put my "Think before write"-hat on.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: generating json without nulls

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

Tim

On 07/05/2015 17:27, David G. Johnston wrote:
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.