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

Question

I have data as follows:

ID   Name    Data
1    Joe     ["Mary","Joe"]
2    Mary    ["Sarah","Mary","Mary"]
3    Bill    ["Bill","Joe"]
4    James   ["James","James","James"]

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:

ID   Name   Last
1    Joe    Mary
2    Mary   Sarah
3    Bill   Joe
4    James  (NULL)

I am getting close - I can select the last element with the following query:

SELECT ID, Name,
(Data::json->(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

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.

Any help or pointers would be most appreciated!

Answer 1

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?