Postgresql -如何查询postgresql数组元素

ghg1uchk 于 2023-01-12 发布在 PostgreSQL

关注(0)|答案(2)|浏览(303)

我在PostgreSQL 13表中有JSON数据。我想以这样一种方式查询这个表，在输出中它将在单独的列中打印数组上的每个元素。
我试着使用下面的查询，其中使用-〉〉操作符，但它没有给我预期的结果，我想我错过了一些东西。
有人能帮帮我吗？

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

样本数据：

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
上表x1c 0d1x的选择查询的预期输出

postgresql

来源：https://stackoverflow.com/questions/75081179/postgresql-how-to-query-postgresql-array-element

2条答案

按热度按时间

nlejzf6q1#

您需要将内部JSON数组转换为常规Postgres数组：

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

然后将其用作派生表来查询各个数组元素：

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

在db<>fiddle.中测试

赞(0）回复(0）举报 2023-01-12

a2mppw5e2#

您需要提取metrics数组的第一个元素，然后选取values元素并访问其中的每个数组元素。这可以使用多个->运算符来完成，例如：

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

或者使用#>>运算符和数组路径：

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

赞(0）回复(0）举报 2023-01-12

我来回答

Postgresql -如何查询postgresql数组元素

2条答案

相关问题

热门标签

最新问答