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