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!

Crucial: your Postgres version? Data type json or jsonb? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 15, 2015 at 4:52

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

`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

3 Comments

pozs Over a year ago

Great answer, but trimming a json string can fail, if that string contains some escaping. There is a (hack-ish, i know, but) more reliable way to do the "cast" in 9.3: ('[' || d::text || ']')::json ->> 0 sqlfiddle.com/#!15/99d11/1

Erwin Brandstetter Over a year ago

Thanks. That's also what Marti Raudsepp suggested in the discussion on plpgsql-general. It's better than the alternatives I had before, so I updated with your expression.

Scott Switzer Over a year ago

Thanks for this - I will test this afternoon. Looks promising!

Collectives™ on Stack Overflow

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

1 Answer 1

`json` in Postgres 9.3

Method 1

Method 2

`json` or `jsonb` in Postgres 9.4

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

json in Postgres 9.3

Method 1

Method 2

json or jsonb in Postgres 9.4

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`json` in Postgres 9.3

`json` or `jsonb` in Postgres 9.4