如何将json数组转换为postgres中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36174881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to turn a json array into rows in postgres
提问by k4ppa
I have a json array stored in my postgres database. The json look like this:
我的 postgres 数据库中存储了一个 json 数组。json 看起来像这样：
[
{
"operation": "U",
"taxCode": "1000",
"description": "iva description",
"tax": "12"
},
{
"operation": "U",
"taxCode": "1001",
"description": "iva description",
"tax": "12"
},
{
"operation": "U",
"taxCode": "1002",
"description": "iva description",
"tax": "12"
}
]
Now I need to SELECT the array so that any element is in a different row of the query result. So the SELECT statement I perform must return the data in this way:
现在我需要选择数组，以便任何元素都在查询结果的不同行中。所以我执行的SELECT语句必须以这种方式返回数据：
data
--------------------------------------------------------------------------------------
{ "operation": "U", "taxCode": "1000", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1001", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1002", "description": "iva description", "tax":"12"}
I tried using the unnest()function
我尝试使用该unnest()功能
SELECT unnest(json_data::json)
FROM my_table
but it doesn't accept the jsonbtype
但它不接受jsonb类型
采纳答案by k4ppa
I post the answer originally written by pozs in the comment section.
我在评论部分发布了最初由 pozs 撰写的答案。
unnest()is for PostgreSQL's array types.
unnest()用于 PostgreSQL 的数组类型。
Instead one of the following function can be used:
可以使用以下功能之一：
json_array_elements(json)(9.3+)jsonb_array_elements(jsonb)(9.4+)json[b]_array_elements_text(json[b])(9.4+)
json_array_elements(json)(9.3+)jsonb_array_elements(jsonb)(9.4+)json[b]_array_elements_text(json[b])(9.4+)
Example:
示例：
select * from json_array_elements('[1,true, [2,false]]')
output value
产值
-------------
| 1 |
-------------
| true |
-------------
| [2,false] |
-------------
Herewhere the documentation for v9.4 can be found.
在这里可以找到 v9.4 的文档。
回答by user2080851
I would suggest using the json_to_recordset command in your case. Your SQL should then be:
我建议在您的情况下使用 json_to_recordset 命令。你的 SQL 应该是：
select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);
The output is:
输出是：
------------------------
| |operation|taxCode |
------------------------
| 1 | "U" | 1000 |
------------------------
| 2 | "U" | 10001 |
------------------------
The columns (or JSON keys) of the example can be freely further expanded.
示例的列（或 JSON 键）可以进一步自由扩展。
回答by Envek
More difficult example:
更难的例子：
Suppose you have a table with rows containing jsonb array each and you wish to splat (or unnest) all that arrays and do some aggregate calculations on records contained in them.
假设您有一个表，每行都包含 jsonb 数组，并且您希望将所有这些数组拆分（或取消嵌套）并对其中包含的记录进行一些聚合计算。
Table (let it be categories):
表（顺其自然categories）：
id | specifics (jsonb)
-----------------------------------------------------------------------------------
1 | [{"name": "Brand", "required": true}, {"name": "Color", "required": false}]
2 | [{"name": "Brand", "required": false}, {"name": "Color", "required": false}]
So, if you want to count, how many required specifics you have, you will need to use such query:
所以，如果你想计算你有多少所需的细节，你需要使用这样的查询：
SELECT specs.name, COUNT(*) AS total
FROM
categories,
jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)
WHERE
specs.required = TRUE
-- AND any other restrictions you need
GROUP BY specs.name
ORDER BY total DESC;
Here FROM x, function(x.column)is a shortened form of a lateral joinwhich effectively joins every row from categorieswith virtual table created by jsonb_to_recordsetfunction from jsonb array in that same row.
这FROM x, function(x.column)是横向连接的缩写形式，它有效地将来自同一行中的 jsonb 数组categories的jsonb_to_recordset函数创建的虚拟表中的每一行连接起来。
