title: Postgres: convert array of elements to multiple rows
body: <p>Using a query along with the functions available in postgres such as <code>string_to_array</code> and <code>string_agg</code>, data from raw tables are converted to following results set.</p>
  
  <pre><code>id, text
  001, {foo,boo,foo}
  002, {"",for,test,friday}
  003, {"","",test,friday,tuesday,foo,boo}
  </code></pre>
  
  <p>Here id is id of a person and text is actually type of array. Now what I am trying to do is generate following structure. </p>
  
  <pre><code>id, text, text_count
  001, foo, 2
  001, boo, 1
  002, test, 1
  002, friday, 1 
  </code></pre>
  
  <p>This is the query I used to get my existing format that I have mentioned, but how can I enhance this query to get the id, text, text_count results. </p>
  
  <pre><code>select id, string_to_array(string_agg(b.text,' '), ' ') as words
  from tableA a,tableB b group by id
  </code></pre>
  
  <p>I would also like to get rid of data with "", I believe they are empty strings in postgres but not really sure.</p>