Get the max value from a slice of a JSON Array?

Question

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

• Evan Carroll: That's a horrible schema for this kind of query. At the very least, use jsonb, even better don't use json, use an int[].

Answer 1

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

• Evan Carroll: I upvoted for doing what he wanted, but there is something to be said here for not doing this at all lol

Answer 2

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.

• McNets: Now it is said. Nice answer. But I think subarray requires start, lenght. subarray(data,f.low,f.high-f.low+1)
• Evan Carroll: Good catch! @McNets
• Maxime: I just started my project and using Postgre, so I did not know about that int[] type. Indeed, it seems way more adapted than Json in this case. I'll have a look at it to change the schema while I still can!