提问人：Artur Vartanyan 提问时间：3/27/2023 更新时间：3/28/2023 访问量：81
在PostgreSQL中解析JSON数组
Parse JSON array in PostgreSQL
问：
我有一个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}]');
答：
0赞
Boorsuk
3/28/2023
#1
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;
评论