Accessing nested array in json blob in PostgreSQL

Question

I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following:

{
"id" : "5",
"data" : "[[1,2,3],[2,3,4],[4,5,6]....]
}

My query needs to take the max of the second value (e.g. 5 in the case given). The following query returns almost what I need:

SELECT
 id,
 (
 SELECT
 string_agg(value :: text, ',') AS list
 FROM
 jsonb_array_elements_text(data -> 'MMC')
 ) as MMCPow
FROM
 comp

gives me:

[1,2,3],[2,3,4],[4,5,6]....

Can anyone get me the rest of the way?

• user1822: Why is the expected max 5? 6 is bigger than 5?
• Timothy Ford: Middle element of each array, so comparing out of the set (2,3,5).

Answer 1

Your example data and your example query do not fit together. There is no JSONB field named 'MMC' in the data, but there is in the query, for example.

Assuming the query is the thing that is correct, then something like this would work:

SELECT
 id,
 (
 SELECT
 max((value->1) :: int) AS _max_
 FROM
 jsonb_array_elements(data -> 'MMC')
 ) as MMCPow
FROM
 comp

Don't use "jsonb_array_elements_text" or ":: text" when you don't want them converted to text, use the -> operator to index into a JSONB array to get the 2nd element (JSONB arrays start counting from 0), and use "max" to take the max.