Search code examples
jsonpostgresqlpostgresql-13

Postgresql - How to query postgresql array element

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 Expected Output

Solution

  • 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