Обсуждение: generating json without nulls

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

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

 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

andreas@visena.com

www.visena.com

 

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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.