Get the max value from a slice of a JSON Array? I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.

The limits Start &amp; End of the slice are contained in an other Json object named Features contained in a table named features

Here is the input:

CREATE TABLE raw (
    id     int PRIMARY KEY
      GENERATED BY DEFAULT AS IDENTITY

    data   json
);

INSERT INTO raw (data) VALUES
  ('[1,2,3,5,6,7,9,10]');

CREATE TABLE features (
    id         int,
    features   json
);

INSERT INTO features (id, features) VALUES
  (1, '{"Start" : 1, "End": 5}');


The output I would like is 7, i.e. the max value of the slice [2,3,5,6,7]

Here is what I came up with looking at other posts, but it does not work...

SELECT
    R."ID",
    F."Features"-&gt;&gt;'Start' AS Start, 
    F."Features"-&gt;&gt;'End' AS End,
    sort_desc((array(select json_array_elements(R."Data")))[F."Features"-&gt;&gt;'Start':F."Features"-&gt;&gt;'End'])[1] as maxData
FROM 
    raw AS R
INNER JOIN 
    features AS F ON R."ID" = F."ID"


The approximate error message I get is concerning sort_desc : 


  No function corresponding to this name or this type of arguments. You
  should convert the type of data
You can unnest json array:

Postgres WITH ORDINALITY:


  When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().


Have a look at this answer of Erwin Brandstetter:


PostgreSQL unnest() with element number




  SELECT
     r."ID",
     MAX(t.elem::int) MaxElem
  FROM   
      raw r
  JOIN
      features f
      ON f."ID" = r."ID"
  JOIN LATERAL
      json_array_elements_text(r."Data")
      WITH ORDINALITY AS t(elem, n) ON TRUE
  WHERE
      n &gt;= (f."Features"-&gt;&gt;'Start')::int + 1 
      AND 
      n &lt;= (f."Features"-&gt;&gt;'End')::int + 1
  GROUP BY
      r."ID";




ID | maxelem
-: | ------:
 1 |       7



db&lt;>fiddle here

Or if you prefer to use intarray module:

SELECT
    r."ID",
    (sort_desc(((ARRAY(SELECT json_array_elements_text(r."Data")))::int[])[(f."Features"-&gt;&gt;'Start')::int + 1:(f."Features"-&gt;&gt;'End')::int + 1]))[1]
FROM
    raw r
JOIN
    features f
    ON f."ID" = r."ID";


rextester here