Postgresql - How to query postgresql array element-CodePudding

I have JSON data in PostgreSQL 13 table. I want to query this table in such a way that in the output it will print each element on the array in a separate column.

I tried using the below query which uses ->> operator but it is not giving me the expected result, I think I am missing something.

Can someone please help me?

select json_data::json->>'dimensions' AS "dimension_value",
json_data::json-> 'metrics'  AS "metrics_value"
from test

Sample Data:

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}')

CodePudding user response：

You need to convert the inner JSON arrays to regular Postgres arrays:

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

then use this as a derived table to query individual arrays elements:

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

Test it in db<>fiddle.

CodePudding user response：

You need to extract the first element of the metrics array, then pick the values element and access each array element from that. This can either be done with multiple -> operators, e.g.:

json_data::json -> 'metrics' -> 0 -> 'values' ->> 0 as "Users",

or using the #>> operator with an array path:

select json_data::json->>'dimensions' AS "dimension_value",
       json_data::json #>> '{metrics,0,values,0}' as "Users",
       json_data::json #>> '{metrics,0,values,1}' as "Sessions",
       json_data::json #>> '{metrics,0,values,2}' as "Organic Searches",
       json_data::json #>> '{metrics,0,values,3}' as "Page Views"
from test