title: Spark extract nested JSON array items using purely SQL-query
body: <p><strong>Note</strong>: this is NOT a duplicate of following (or several other similar discussions)</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/q/25224740/3679900">Spark SQL JSON dataset query nested datastructures</a></li>
  <li><a href="https://stackoverflow.com/q/49133252/3679900">How to use Spark SQL to parse the JSON array of objects</a></li>
  <li><a href="https://stackoverflow.com/q/28332494/3679900">Querying Spark SQL DataFrame with complex types</a></li>
  </ul>
  
  <hr>
  
  <p>I have a <code>Hive</code> table that I must read and <strong>process purely via <code>Spark</code>-SQL-query</strong>. This table has a <code>string</code>-type column, that contains <em><code>JSON</code> dumps from APIs</em>; so expectedly, it has <strong>deeply nested stringified JSONs</strong>.</p>
  
  <p>Lets take this example (it depicts the exact depth / complexity of data that I'm trying to process)</p>
  
  <pre><code>{
      "key1": ..
      "key2": ..
      ..
      "bill_summary": {
          "key1": ..
          "key2": ..
          ..
          "items": [
              {
                  "item": {
                      "key1": ..
                      "key2": ..
                      ..
                      "type": "item_type_1"
                      ..
                      "total_cost": 57.65
                      ..
                  }
              },
              {
                  "item": {
                      "key1": ..
                      "key2": ..
                      ..
                      "total_cost": 23.31
                      ..
                  }
              }
              ..
              {
                  "item": {
                      "key1": ..
                      "key2": ..
                      ..
                      "type": "item_type_1"
                      ..
                  }
              }
          ]
          ..
      }
      ..
  }
  </code></pre>
  
  <p>I'm interested in the <code>items</code> array. I'm able to access it via</p>
  
  <pre><code>get_json_object(get_json_object('$.bill_summary'), '$.items') AS items
  </code></pre>
  
  <p>Now here's the problem</p>
  
  <ul>
  <li>I need to take out all (<code>type</code>, <code>total_cost</code>) tuples from the array</li>
  <li>But I only need those entries where both these are present, whereas several <code>item</code> objects have either of those or none of them</li>
  </ul>
  
  <hr>
  
  <ul>
  <li>While I've also managed to separately pick all <code>type</code> fields and <code>total_cost</code> fields into <em>two separate arrays</em>, but due to second limitation above (absent fields), I end up <em>loosing the relationship</em>.</li>
  <li>What I get (using following snippet) in the end are two arrays (possibly of different lengths), but with no certainty that <em>corresponding elements of each array belong to same item or not</em></li>
  </ul>
  
  <p><em>this snippet lists only part of my rather long SQL-query. It employs <a href="https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression" rel="nofollow noreferrer">CTE</a></em></p>
  
  <pre><code>..
  split(get_json_object(get_json_object(var6, '$.bill_summary'), '$.items[*].item.type'), ',') AS types_array,
  split(get_json_object(get_json_object(var6, '$.bill_summary'), '$.items[*].item.total_cost'), ',') AS total_cost_array
  ..
  </code></pre>
  
  <hr>
  
  <p>Now here are the limitations</p>
  
  <ul>
  <li>I have no control over source <code>Hive</code> table schema or its data</li>
  <li>I want to do this using <strong>purely <code>Spark</code>SQL-query</strong></li>
  <li>I <strong>cannot use <code>DataFrame</code> manipulation</strong></li>
  <li>I do not want to employ a <a href="https://stackoverflow.com/a/41331005/3679900">registered <code>udf</code></a> (I'm keeping that as a last resort)</li>
  </ul>
  
  <hr>
  
  <p>I've spent several hours on docs and forums, but the <a href="https://spark.apache.org/docs/latest/api/sql/index.html" rel="nofollow noreferrer">Spark-SQL docs</a> are sparse and <a href="https://stackoverflow.com/a/36850870/3679900">discussions</a> mostly revolve around <code>DataFrame</code> API, which I cannot use. Is this problem even solvable by SQL-query alone?</p>