我在表中有列“元素”，它有一個 json(array json) 行值，看起來像這樣
| 元素 |
|---|
| [{"key":12,"value":"qw"},{"key":13,"value":"fa"}] |
| [{"key":32,"value":"24"},{"key":321,"value":"21"}] |
我想為每一行創建一列陣列，其中包含從該行的 json 值中提取的鍵，我想要的列“結果”可能如下所示
| 元素 | 結果 |
|---|---|
| [{"key":12,"value":"qw"},{"key":13,"value":"fa"}] | {12,13} |
| [{"key":32,"value":"24"},{"key":321,"value":"21"}] | {32,321} |
有辦法嗎？謝謝你
uj5u.com熱心網友回復：
架構 (PostgreSQL v13)
CREATE TABLE test (
elements json
);
INSERT INTO test VALUES ('[{"key":12,"value":"qw"},{"key":13,"value":"fa"}]');
INSERT INTO test VALUES ('[{"key":32,"value":"24"},{"key":321,"value":"21"}]');
查詢 #1
select elements::text, array_agg(cast(value->>'key' as integer)) as result
from test, json_array_elements(elements)
group by 1
ORDER BY 1;
| 元素 | 結果 |
|---|---|
| [{"key":12,"value":"qw"},{"key":13,"value":"fa"}] | 12,13 |
| [{"key":32,"value":"24"},{"key":321,"value":"21"}] | 32,321 |
在 DB Fiddle 上查看
uj5u.com熱心網友回復：
select elements::text,
array_agg(value->>'key')
from your_table, json_array_elements(elements)
group by 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/458877.html
標籤：sql PostgreSQL