Question 90189 in PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search