title: Right way to select not empty values in a json column with Laravel > 5.6?
body: <p>I can't seem to able to wrap my head around this.</p>
  
  <p>I got several translations in a json column called <code>text</code>, and I want to select only those records that have not empty translations for a specific language (not set, or not <code>null</code>). </p>
  
  <p>I struggle in creating a query that doesn't return a record if the value is set to <code>null</code>.
  Here are some <code>text</code> column json values, and I need a query that returns only the first record in this example, where I have a translation for the <code>fr</code> value:</p>
  
  <pre><code>text: "{"de": "danke", "fr": "merci"}"
  text: "{"de": "hallo", "fr": null}"
  text: "{"de": "und"}"
  </code></pre>
  
  <p>Here is what I tried so far:</p>
  
  <pre><code>parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") is not null' )-&gt;get();
  </code></pre>
  
  <p>-> returns </p>
  
  <pre><code>text: "{"de": "danke", "fr": "merci"}"
  text: "{"de": "hallo", "fr": null}"
  </code></pre>
  
  <p>Bummer. It skips the record where <code>fr</code> is not set at all (that's ok), but keeps selecting the record where <code>fr</code> is set to <code>null</code>. So, I need to adjust the query to exclude the record with <code>"fr": null</code></p>
  
  <p>Let's approach this step by step. When I try a positive match search for a string like this:</p>
  
  <pre><code>parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") = "merci"' )-&gt;get();
  </code></pre>
  
  <p>-> returns</p>
  
  <pre><code>text: "{"de": "danke", "fr": "merci"}"
  </code></pre>
  
  <p>That is correct. Now, it would seem easy to find a way to switch this around and exclude records with <code>"fr": null</code>. But no luck. </p>
  
  <pre><code>parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") &lt;&gt; "merci"' )-&gt;get();
  </code></pre>
  
  <p>-> returns <strong>only</strong> the record where <code>fr</code> is set, but is not equal to <code>merci</code>. It skips the record where <code>fr</code> is not set.</p>
  
  <pre><code>text: "{"de": "hallo", "fr": null}"
  </code></pre>
  
  <p>Let's try to exclude records where fr is set to null. But no luck either:</p>
  
  <pre><code>parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") &lt;&gt; "null"' )-&gt;get();
  </code></pre>
  
  <p>-> returns</p>
  
  <pre><code>text: "{"de": "danke", "fr": "merci"}"
  text: "{"de": "hallo", "fr": null}"
  </code></pre>
  
  <p>Or,</p>
  
  <pre><code>parent::whereRaw( 'JSON_EXTRACT(text, "$.fr") &lt;&gt; null' )-&gt;get();
  </code></pre>
  
  <p>-> returns an empty result. </p>
  
  <p>How the heck can I exclude <code>fr</code> values that are not set or are set to <code>null</code>?</p>