title: Postgresql: convert text representing key - value to json
body: <p>I have a table with a string column containing values like this: 'ID: 1, name: john doe, occupation: salesmen'. I want to convert this into a column of JSON objects like this: {"ID" : "1", "name" : "john doe", "occupation" : "salesmen"}</p>
  
  <p>For now my solution is:</p>
  
  <pre><code>    WITH 
      lv1 as(SELECT regexp_split_to_table('ID: 1, name: john doe, occupation: salesmen', ', ') record)
      , lv2 as (SELECT regexp_split_to_array(record, ': ') arr from lv1)
  
     SELECT 
         json_object(
             array_agg(arr[1])
             , array_agg(arr[2])
         )
     FROM lv2
  </code></pre>
  
  <p>The problem is that the string actually contains nearly 100 key - value pair and the table has millions of rows, so using regex_split_to_table will make this table explode. Is there any efficient way to do this in Postgresql?</p>