title: Postgres string manipulation
body: <p>I have a field in my table that contains a string that can follow any of 3 formats:</p>
  <ol>
  <li>null</li>
  <li>&quot;[string]&quot;</li>
  <li>&quot;Following fields matched: {string=string, string=string, string=string..}&quot;</li>
  </ol>
  <p>For 1 I need output : null
  For 2 I need output : 
  For 3 I need to split each pair into its each row with columns [key] &amp; [value]</p>
  <p>Now, I have solved this challenge, I just think that I have done it in the most eloquent or efficient manner.</p>
  <p>Would be grateful if anyone could point me to a better solution ?
  Thanks</p>
  <pre><code>SELECT 
    h.nid, 
    trim(d.key) as &quot;key&quot;, 
    trim(d.value) as &quot;value&quot;
  FROM 
    (
            SELECT 
                r.*,
                cast(
                    replace(replace(replace(replace(
                          case 
                              when left(r.relation_details,25) = 'Following fields matched:' 
                              then right(r.relation_details, char_length(r.relation_details)-25)
                              when left(r.relation_details,1) = '[' 
                              then '{' || r.relation_details || '=' || r.relation_details || '}'
                              else null 
                          end 
                          ,'=' ,'&quot;:&quot;'),',' ,'&quot;,&quot;'),'{' ,'{&quot;'),'}' ,'&quot;}')
                as json) as json
              FROM podium_core.pd_entity_relation r
    ) h
    JOIN 
    json_each_text(h.json) d ON true
  </code></pre>