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 slices(id INT, facebook_results JSON)
INSERT INTO slices VALUES(169, '{ "table":{ "matches": [ { "table":{ "nodes":[ { "table":{ "crawl_date":"2013-06-21", "url":"http://example.com" } } ], "size":7962624, "score":47.059, "width":3456, "format":"MP4", "domain":"example.com" } } ] }}')
1 rows affected
SELECT
id AS slice_id,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table'-> 'size' AS match_size,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'score' AS match_score,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'width' AS match_width,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'format' AS match_format,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'domain' AS match_domain,
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'crawl_date' AS node_crawl_date,
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'url' AS node_url
FROM slices
WHERE id = 169
| slice_id | match_size | match_score | match_width | match_format | match_domain | node_crawl_date | node_url |
|---|---|---|---|---|---|---|---|
| 169 | 7962624 | 47.059 | 3456 | "MP4" | "example.com" | "2013-06-21" | "http://example.com" |
SELECT
id AS slice_id,
s.t -> 'size' AS match_size,
s.t -> 'score' AS match_score,
s.t -> 'width' AS match_width,
s.t -> 'format' AS match_format,
s.t -> 'domain' AS match_domain,
s.t2-> 'crawl_date' AS node_crawl_date,
s.t2-> 'url' AS node_url
FROM slices
,LATERAL (SELECT json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table',
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table'
) s(t,t2)
WHERE id = 169
| slice_id | match_size | match_score | match_width | match_format | match_domain | node_crawl_date | node_url |
|---|---|---|---|---|---|---|---|
| 169 | 7962624 | 47.059 | 3456 | "MP4" | "example.com" | "2013-06-21" | "http://example.com" |