agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: generating json without nulls
Date: Tue, 19 May 2015 10:48:40 +0100
Message-ID: <555B06F8.3020000@gmail.com> (raw)
In-Reply-To: <CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com>
References: <VisenaEmail.70.7125f92dc9db25c2.14d2ef26624@tc7-visena>
<554B84C0.1070007@gmail.com>
<CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
>
view thread (6+ messages)
Message-ID: <555B06F8.3020000@gmail.com>
Permalink: ../555B06F8.3020000@gmail.com/
Also on: postgresql.org/message-id/555B06F8.3020000@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tdudgeon.ml@gmail.com
Subject: Re: generating json without nulls
In-Reply-To: <555B06F8.3020000@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox