Postgresql - How to query postgresql array element

Question

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

DB FIDDLE

Expected output of select query for above table

Answer 1

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.

Answer 2

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