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 & 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"->>'Start' AS Start, 
    F."Features"->>'End' AS End,
    sort_desc((array(select json_array_elements(R."Data")))[F."Features"->>'Start':F."Features"->>'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 >= (f."Features"->>'Start')::int + 1 
      AND 
      n <= (f."Features"->>'End')::int + 1
  GROUP BY
      r."ID";

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

db<>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"->>'Start')::int + 1:(f."Features"->>'End')::int + 1]))[1]
FROM
    raw r
JOIN
    features f
    ON f."ID" = r."ID";

rextester here

This is all around a horrible schema. You shouldn’t be using json (as compared with jsonb) at all, ever (practically). If you’re querying on the field, it should be jsonb. In your case, that’s still a bad idea though, you likely want an sql array..

CREATE TABLE raw (
    raw_id   int  PRIMARY KEY
      GENERATED BY DEFAULT AS IDENTITY,
    data     int[]
);

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

CREATE TABLE features (
    feature_id   int REFERENCES raw,
    low          smallint,
    high         smallint
);

INSERT INTO features ( feature_id, low, high ) VALUES ( 1, 1, 5 );

Now you can query it like this, note remember sql is 1-based,

SELECT max(unnest)
FROM raw
CROSS JOIN features AS f
CROSS JOIN LATERAL unnest(data[f.low:f.high]);

Also check out the intarray module, because it’ll optimize the above,

CREATE EXTENSION intarray;

SELECT max(unnest)
FROM raw
CROSS JOIN features AS f
CROSS JOIN LATERAL unnest(subarray(data,f.low,f.high-f.low+1))

You can further optimize this if you know you just need the last element of the array.

Note if this is a GIS problem, you’re still probably doing it wrong, but at least this method is sane.