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?.
CREATE TABLE tbl(id int, fixtures json);
INSERT INTO tbl VALUES
(1, '{"fixture_1":"fixture1", "fixture_2":"fixture2", "fixture_123":"fixture123"}')
, (2, '{"fixture_1":"fixture1","fixture_123":"fixture123"}') -- dupes
, (3, '{"fixture_6":"fixture6", "fixture_66":"fixture66"}')
;
CREATE TABLE
INSERT 0 3
-- all IDs (incl. dupes)
SELECT split_part(json_object_keys(fixtures), '_', 2)::int AS id
FROM tbl
ORDER BY id;
| id |
|---|
| 1 |
| 1 |
| 2 |
| 6 |
| 66 |
| 123 |
| 123 |
SELECT 7
-- distinct IDs
SELECT DISTINCT split_part(json_object_keys(fixtures), '_', 2)::int AS id
FROM tbl
ORDER BY id;
| id |
|---|
| 1 |
| 2 |
| 6 |
| 66 |
| 123 |
SELECT 5
-- all IDs per row (no dupes by definition - if your key names are consitent)
SELECT t.id, k.ids
FROM tbl t
LEFT JOIN LATERAL (
SELECT ARRAY (SELECT split_part(json_object_keys(t.fixtures), '_', 2)::int)
) k(ids) ON true;
| id | ids |
|---|---|
| 1 | {1,2,123} |
| 2 | {1,123} |
| 3 | {6,66} |
SELECT 3