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?.

CREATE TABLE IF NOT EXISTS test
(
    json_data character varying 
);


INSERT INTO test (json_data) VALUES ('{"dimensions":["20230105","(not set)","New Visitor","(direct) / (none)","(not set)","(not set)"],"metrics":[{"values":["6","6","0","6"]}],"nextPageToken":"50","rowCount":62,"isDataGolden":true}')


CREATE TABLE



INSERT 0 1


SELECT * FROM test


json_data


{"dimensions":["20230105","(not set)","New Visitor","(direct) / (none)","(not set)","(not set)"],"metrics":[{"values":["6","6","0","6"]}],"nextPageToken":"50","rowCount":62,"isDataGolden":true}



SELECT 1


select
	dimensions[1] as "Date",
	dimensions[2] as "Continent",
	dimensions[3] as "User Type",
	dimensions[4] as "Source/Medium",
	dimensions[5] as "Campaign",
	dimensions[6] as "Social Network",
	metrics[1] as "Users",
	metrics[2] as "Sessions",
	metrics[3] as "Organic Searches",
	metrics[4] as "Page Views"
from (
	select 
		array(select json_array_elements_text(json_data::json->'dimensions')) as dimensions,
		array(select json_array_elements_text(json_data::json->'metrics'->0->'values')) as metrics
	from test
	) s



Date
Continent
User Type
Source/Medium
Campaign
Social Network
Users
Sessions
Organic Searches
Page Views


20230105
(not set)
New Visitor
(direct) / (none)
(not set)
(not set)
6
6
0
6



SELECT 1