0

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

Vikas J
  • 795
  • 3
  • 14
  • 31
  • 1
    JSON data should be stored in a column defined as `jsonb`, not a `varchar` or `text` column –  Jan 11 '23 at 10:02
  • True! But table schema is not in my control. Besides, If I can partially get the data using ->> operator I thought I can also get complete data to meet the expected results, may be by using something more along with ->> operator – Vikas J Jan 11 '23 at 10:08
  • How do you know which array element corresponds to "Users" or "Sessions"? Is the position of those always the same? –  Jan 11 '23 at 10:29
  • Yes! So the value of first element of the metrics array which is 6 always will point to Users second element of the metrics array which is again 6 will point to Sessions, third element's value 0 will point to Organic Searches and so on. – Vikas J Jan 11 '23 at 10:34

2 Answers2

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.

klin
  • 112,967
  • 15
  • 204
  • 232
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