title: How do I solve the Redshift error: "Missing data for not-null field"?
body: <p>I have a nested JSON field that looks like this:</p>
  <pre><code>trend
  {&quot;trend&quot;:0,&quot;abs&quot;:0,&quot;per&quot;:null}
  </code></pre>
  <p>I want to query and get the values that are inside, so I have a case statement in order to get the values:</p>
  <pre><code>select
  (CASE WHEN trend like '%&quot;trend&quot;%' THEN json_extract_path_text(trend, 'trend') ELSE NULL END)::SMALLINT,
  (CASE WHEN trend like '%&quot;abs&quot;%' THEN json_extract_path_text(trend, 'abs') ELSE NULL END)::INTEGER,
  (CASE WHEN trend like '%&quot;per&quot;%' THEN json_extract_path_text(trend, 'per') ELSE NULL END)::DOUBLE PRECISION
  from staging_raw.table
  </code></pre>
  <p>The issue is in the last field <code>per</code>, because it's <code>null</code> already I am getting this error message in Redshift:</p>
  <p><a href="https://i.stack.imgur.com/kyLFk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kyLFk.png" alt="Redshift Error: Missing data for not-null field" /></a></p>
  <p>I tried to use the clause:</p>
  <pre><code>(CASE WHEN trend like '%&quot;per&quot;%' and json_extract_path_text(trend, 'per') is not NULL THEN json_extract_path_text(trend, 'per')  ELSE NULL END)::DOUBLE PRECISION
  </code></pre>
  <p>but I still get the same error. How can this be resolved, if the source is like this?</p>