在PostgreSQL中解析JSON数组

at0kjp5o 于 2023-03-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(207)

我有一个json，其中存储了一个数据数组。数据是基于键值存储的。我需要将我的数据解析为两列，其中键将在第一列，对应的值将在第二列。
使用PostgreSQL 15

select * from json_populate_recordset(null::json_parse_type, '[{"vartanyan":1},{"ermilova":2},{"dyakonov":2},{"sokornov":2},
{"shevchenko":2},{"kreslavskaya":2},{"vartanyan":2},{"jusupov":2},{"kamenkov":2},{"davidiuk":2},{"markhipova":2},
{"voronova":2},{"karamyshev":2},{"cheryabkin":2},{"sstepanov":2},{"shumeeva":2},{"balanda":2},{"mineev":2}]');

postgresql

来源：https://stackoverflow.com/questions/75856481/parse-json-array-in-postgresql

1条答案

按热度按时间

yzuktlbb1#

WITH data AS (
    SELECT '[{"vartanyan":1},{"ermilova":2},{"dyakonov":2},{"sokornov":2},
            {"shevchenko":2},{"kreslavskaya":2},{"vartanyan":2},{"jusupov":2},{"kamenkov":2},{"davidiuk":2},{"markhipova":2},
            {"voronova":2},{"karamyshev":2},{"cheryabkin":2},{"sstepanov":2},{"shumeeva":2},{"balanda":2},{"mineev":2}]'::jsonb
)
SELECT key, el->>key val
FROM jsonb_array_elements((SELECT * FROM data)) el
CROSS JOIN jsonb_object_keys(el) key;

赞(0）回复(0）举报 2023-03-29

我来回答

在PostgreSQL中解析JSON数组

1条答案

相关问题

热门标签

最新问答