title: How to aggregate several columns into a JSON file in HIVE and avoid nulls
body: <pre><code>user_id reservation_id  nights  price 
  --------------------------------------
  AAA     10001             1     100
  AAA     10002             1     120
  BBB     20003             7     350
  ccc     10005                   150
  DDD     10007             3
  CCC     10006             5      
  </code></pre>
  
  <p>to  </p>
  
  <pre><code>user_id    reservation_details
  AAA        [{"nights":"1", "price":"100"}, {"nights":"1","price":"120"}]
  BBB        [{"nights":"7", "price":"350"}]
  CCC        [{"price":"150"}, {"nights":"3"}]
  DDD        [{"nights":"5"}]
  </code></pre>
  
  <p>Here my query is </p>
  
  <pre><code>select user_id
         ,concat(" 
                 {",concat_ws(',',collect_list(concat(string(reservation_id),": 
  
                {'nights':",string(nights),",'price':",string(price),"}"))),"}")  as 
        reservation_details 
  from  mytable
  group by user_id    
  </code></pre>
  
  <p>I want to eliminate the columns which have value as nulls and convert that single quotes into double quotes which looks like a exact JSON.</p>