Message-ID: <555B06F8.3020000@gmail.com>
Date: Tue, 19 May 2015 10:48:40 +0100
From: Tim Dudgeon <tdudgeon.ml@gmail.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10;
 rv:31.0) Gecko/20100101 Thunderbird/31.6.0
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: generating json without nulls
References: <VisenaEmail.70.7125f92dc9db25c2.14d2ef26624@tc7-visena>
	<554B84C0.1070007@gmail.com>
 <CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------080201090302040500040401"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------080201090302040500040401
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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.
>


--------------080201090302040500040401
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Thanks.<br>
    Using the CASE ... THEN ... ELSE ... END approach seems to work best
    for me.<br>
    Not especially elegant, but it does the job.<br>
    <br>
    Tim<br>
    <br>
    <div class="moz-cite-prefix">On 07/05/2015 17:27, David G. Johnston
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAKFQuwajBFsDgLqjkfbQGKM7tm9i0mUeqhR87pdfk0o027emOQ@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_default"
          style="font-family:arial,helvetica,sans-serif"><span
            style="font-family:arial,sans-serif">On Thu, May 7, 2015 at
            8:29 AM, Tim Dudgeon </span><span dir="ltr"
            style="font-family:arial,sans-serif">&lt;<a
              moz-do-not-send="true" href="mailto:tdudgeon.ml@gmail.com"
              target="_blank">tdudgeon.ml@gmail.com</a>&gt;</span><span
            style="font-family:arial,sans-serif"> wrote:</span><br>
        </div>
        <div class="gmail_extra">
          <div class="gmail_quote">
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
              <div bgcolor="#FFFFFF" text="#000000"> That's not going to
                work. I want the row, I just don't want the values that
                are null.</div>
            </blockquote>
            <div><br>
            </div>
            <div>
              <div class="gmail_default"
                style="font-family:arial,helvetica,sans-serif">Only
                thing that comes to mind:<br>
              </div>
            </div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">1. Use the
              conversion function to get the json structure with nulls.</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">2. Use an
              explode function to convert the json into a table
              structure with (key, value) columns.</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">3. Filter
              that table where value is not null.</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">4. Convert
              the remaining entries into arrays</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">5. Pass the
              two arrays back into the json_object(keys text[], values
              text[])</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif"><br>
            </div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">You could
              dynamically build up a literal string array but the syntax
              challenges scare me:</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">json_object('{'
              ||</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">CASE WHEN
              col1 IS NULL THEN '' ELSE '"col1",' || val1 || '"' END ||</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">CASE WHEN
              col2 IS NULL THEN '' ELSE '"col2",' || val2 || '"' END ||</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">'}'::text[])</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif"><br>
            </div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif">David J.</div>
            <div class="gmail_default"
              style="font-family:arial,helvetica,sans-serif"><br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------080201090302040500040401--