Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

WITH t AS (
   SELECT '[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]'::json as data
)
SELECT 
    *
FROM t

data

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]

SELECT 1

WITH t AS (
   SELECT '[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]'::json as data
)
SELECT 
    *
FROM t,
    json_array_elements(data) elem

data
value

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "26/11/2020 23:27","note": "test1"}

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "22/11/2020 22:59","note": "test2"}

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "18/11/2020 17:08","note": "test3"}

SELECT 3

WITH t AS (
   SELECT '[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]'::json as data
)
SELECT 
    *
FROM t,
    json_array_elements(data) elem
ORDER BY to_timestamp(elem ->> 'date', 'DD/MM/YYYY HH24:MI') DESC

data
value

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "26/11/2020 23:27","note": "test1"}

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "22/11/2020 22:59","note": "test2"}

[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]
{"date": "18/11/2020 17:08","note": "test3"}

SELECT 3

WITH t AS (
   SELECT '[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]'::json as data
)
SELECT 
    value
FROM t,
    json_array_elements(data) elem
ORDER BY to_timestamp(elem ->> 'date', 'DD/MM/YYYY HH24:MI') DESC

value

{"date": "26/11/2020 23:27","note": "test1"}

{"date": "22/11/2020 22:59","note": "test2"}

{"date": "18/11/2020 17:08","note": "test3"}

SELECT 3

WITH t AS (
   SELECT '[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]'::json as data
)
SELECT 
    value
FROM t,
    json_array_elements(data) elem
ORDER BY to_timestamp(elem ->> 'date', 'DD/MM/YYYY HH24:MI') DESC
LIMIT 1

value

{"date": "26/11/2020 23:27","note": "test1"}

SELECT 1

data	value
[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]	{"date": "26/11/2020 23:27","note": "test1"}
[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]	{"date": "22/11/2020 22:59","note": "test2"}
[{"date": "26/11/2020 23:27","note": "test1"},{"date": "22/11/2020 22:59","note": "test2"},{"date": "18/11/2020 17:08","note": "test3"}]	{"date": "18/11/2020 17:08","note": "test3"}