title: How do I return NULL conditionally from a JSON_QUERY
body: <p>How do I return a JSON_QUERY that returns NULL (or no property at all) if the selected value from the sub-query (or left join) is empty?</p>
  <p>Here is a simple example (imagine NULL below is a sub-query):</p>
  <pre><code>SELECT 
      JSON_QUERY((SELECT NULL as SomeColumn 
                  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test
  </code></pre>
  <p>This returns and empty JSON object - which is not what I want:</p>
  <pre><code>Test
  ----------------
  {}
  </code></pre>
  <p>Since NULL is being returned from the sub-query, I want it to return NULL if the subquery returns NULL, otherwise it should return JSON.</p>
  <p>i.e.</p>
  <p>When the Subquery returns NULL, the JSON result should be:</p>
  <pre><code>Test
  ----------------
  NULL
  </code></pre>
  <p>When the Subquery returns JSON, the JSON result should be:</p>
  <pre><code>Test
  ----------------
  { someColumn: 'some json' ... }
  </code></pre>
  <p>How do I do this with a JSON query?</p>
  <p>[Edit]</p>
  <p>I tried this, thinking I could just do a CASE statement:</p>
  <pre><code>SELECT 
      JSON_QUERY((SELECT JSON_VALUE('{&quot;SomeColumn&quot;:&quot;Test&quot;}', '$') AS SomeColumn 
                  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test
  </code></pre>
  <p>But this won't select the root. The above returns:</p>
  <pre><code>Test
  ------
  {}
  </code></pre>
  <p><em>USE CASE</em></p>
  <p>The use case that I'm after is this.</p>
  <p>When I return a JSON query, I want to be able to return something like this when the address exists:</p>
  <pre class="lang-json prettyprint-override"><code>   { 
        name: 'John',
        address: {
           line1: '123 elm st'
        }
     }
  </code></pre>
  <p>OR if there is no address record, I want it to return:</p>
  <pre class="lang-json prettyprint-override"><code>   { 
        name: 'John',
        address: NULL
     }
  </code></pre>
  <p>OR</p>
  <pre class="lang-json prettyprint-override"><code>   { 
        name: 'John'
     }
  
  </code></pre>
  <p>I DON'T want it to return</p>
  <pre class="lang-json prettyprint-override"><code>   { 
        name: 'John',
        address: {}
     }
  </code></pre>