agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedgenerating json without nulls
6+ messages / 3 participants
[nested] [flat]
* generating json without nulls
@ 2015-05-07 10:56 Tim Dudgeon <tdudgeon.ml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tim Dudgeon @ 2015-05-07 10:56 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generating json without nulls
@ 2015-05-07 15:16 Andreas Joseph Krogh <andreas@visena.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Joseph Krogh @ 2015-05-07 15:16 UTC (permalink / raw)
To: pgsql-sql
På torsdag 07. mai 2015 kl. 12:56:42, skrev Tim Dudgeon <tdudgeon.ml@gmail.com
<mailto: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 <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generating json without nulls
@ 2015-05-07 15:29 Tim Dudgeon <tdudgeon.ml@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tim Dudgeon @ 2015-05-07 15:29 UTC (permalink / raw)
To: pgsql-sql
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 <mailto: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 <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com;
> <https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generating json without nulls
@ 2015-05-07 16:27 David G. Johnston <david.g.johnston@gmail.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2015-05-07 16:27 UTC (permalink / raw)
To: Tim Dudgeon <tdudgeon.ml@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generating json without nulls
@ 2015-05-07 19:52 Andreas Joseph Krogh <andreas@visena.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2015-05-07 19:52 UTC (permalink / raw)
To: pgsql-sql
På torsdag 07. mai 2015 kl. 17:29:04, skrev Tim Dudgeon <tdudgeon.ml@gmail.com
<mailto: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 andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generating json without nulls
@ 2015-05-19 09:48 Tim Dudgeon <tdudgeon.ml@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tim Dudgeon @ 2015-05-19 09:48 UTC (permalink / raw)
To: pgsql-sql
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
> <mailto: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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-05-19 09:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-07 10:56 generating json without nulls Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-05-07 15:16 ` Andreas Joseph Krogh <andreas@visena.com>
2015-05-07 15:29 ` Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-05-07 16:27 ` David G. Johnston <david.g.johnston@gmail.com>
2015-05-19 09:48 ` Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-05-07 19:52 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox