0

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?

  • Why is the expected max 5? 6 is bigger than 5? – a_horse_with_no_name Jul 26 at 17:13
  • Middle element of each array, so comparing out of the set (2,3,5). – Timothy Ford Jul 26 at 17:17
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.