PostgreSQL – Accessing Nested Array in JSON Blob

arrayjsonpostgresql

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?

Best Answer

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.

Related Question