Need to select a JSON array element dynamically from a postgresql table

json in Postgres 9.3

This is hard in pg 9.3, because useful functionality is missing.

Method 1

Unnest in a LEFT JOIN LATERAL (clean and standard-conforming), trim double-quotes from json after casting to text. See links below.

SELECT DISTINCT ON (1)
       t.id, t.name, d.last
FROM   tbl t
LEFT   JOIN LATERAL (
  SELECT ('[' || d::text || ']')::json->>0 AS last
  FROM   json_array_elements(t.data) d
  ) d ON d.last <> t.name
ORDER  BY 1, row_number() OVER () DESC;

While this works, and I have never seen it fail, the order of unnested elements depends on undocumented behavior. See links below!
Improved the conversion from json to text with the expression provided by @pozs in the comment. Still hackish, but should be safe.

Method 2

SELECT DISTINCT ON (1)
       id, name, NULLIF(last, name) AS last
FROM (
   SELECT t.id, t.name
        ,('[' || json_array_elements(t.data)::text || ']')::json->>0 AS last
        , row_number() OVER () AS rn
   FROM   tbl t
   ) sub
ORDER  BY 1, (last = name), rn DESC;

• Unnest in the SELECT list (non-standard).
• Attach row number (rn) in parallel (more reliable).
• Convert to text like above.
• The expression (last = name) in the ORDER BY clause sorts matching names last (but before NULL). So a matching name is only selected if no other name is available. Last link below. In the SELECT list, NULLIF replaces a matching name with NULL, arriving at the same result as above.

SQL Fiddle.

json or jsonb in Postgres 9.4

pg 9.4 ships all the necessary improvements:

SELECT DISTINCT ON (1)
       t.id, t.name, d.last
FROM   tbl t
LEFT   JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn)
       ON d.last <> t.name
ORDER  BY d.rn DESC;

Use jsonb_array_elements_text() for jsonb. All else the same.

json / jsonb functions in the manual

Related answers with more explanation:

• How to turn json array into postgres array?
• PostgreSQL unnest() with element number
• Index for finding an element in a JSON array
• Time based priority in Active Record Query
• Select first row in each GROUP BY group?