title: Clean way to query complex JSON in Postgresql
body: <p>I have JSON data stored in a JSONB field in my postgresql 9.5 DB.</p>
  
  <p>Is there a way of making sub-objects columns without knowing which column is a sub-object? </p>
  
  <p>JSON example in question: </p>
  
  <pre><code>{
     "a":1,
     "b":[1,2,3],
     "c":"bar",
     "d":{
        "key1":"value1",
        "key2":"value2"
     }
   }
  </code></pre>
  
  <p>I can use the following to get all of the keys from a JSON object.</p>
  
  <p><code>SELECT * FROM json_object_keys('{"a":1,"b":[1,2,3],"c":"bar", "d":{"key1":"value1", "key2":"value2"}}')</code></p>
  
  <p>At that point I can then use json_to_record() but I would like to split the column out to their own separate fields.</p>
  
  <p><code>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar", "d":{"key1":"value1", "key2":"value2"}}') as x(a int, b text, c text, d text)</code></p>
  
  <p>gets me </p>
  
  <pre><code>a| b       | c   | d     
  1| [1,2,3] | bar | {"key1":"value1", "key2":"value2"}
  </code></pre>
  
  <p>Is there a way to get something like this back, preferably in a single query?</p>
  
  <pre><code>--------------------------------------------------------------------
  a| b       | c   | d                                  | key1  | key2     
  1| [1,2,3] | bar | {"key1":"value1", "key2":"value2"} |value1 |value2
  </code></pre>