title: Need to select a JSON array element dynamically from a postgresql table
body: <p>I have data as follows:</p>
  
  <pre><code>ID   Name    Data
  1    Joe     ["Mary","Joe"]
  2    Mary    ["Sarah","Mary","Mary"]
  3    Bill    ["Bill","Joe"]
  4    James   ["James","James","James"]
  </code></pre>
  
  <p>I want to write a query that selects the LAST element from the array, which does not equal the Name field.  For example, I want the query to return the following results:</p>
  
  <pre><code>ID   Name   Last
  1    Joe    Mary
  2    Mary   Sarah
  3    Bill   Joe
  4    James  (NULL)
  </code></pre>
  
  <p>I am getting close - I can select the last element with the following query:</p>
  
  <pre><code>SELECT ID, Name,
  (Data::json-&gt;(json_array_length(Data::json)-1))::text AS Last
  FROM table;
  
  ID    Name    Last
  1     Joe     Joe
  2     Mary    Mary
  3     Bill    Joe
  4     James   James
  </code></pre>
  
  <p>However, I need one more level - to evaluate the last item, and if it is the same as the name field, to try the next to last field, and so on.</p>
  
  <p>Any help or pointers would be most appreciated!</p>