如何在 postgres 中将 json 数组转换为行

我有一个 json 数组存储在我的 postgres 数据库。 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"
}
]

现在我需要 SELECT数组，以便任何元素都位于查询结果的不同行中。因此，我执行的 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"}

我尝试使用 unnest()函数

SELECT unnest(json_data::json)
FROM my_table

但它不接受 jsonb类型。

149646 次浏览
小开

I would suggest using the json_to_recordset command in your case. Your SQL should then be:

select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);

输出结果是:

------------------------
|   |operation|taxCode |
------------------------
| 1 |   "U"   |   1000 |
------------------------
| 2 |   "U"   |  10001 |
------------------------

示例的列(或 JSON 键)可以自由地进一步展开。

小开
最佳答案

我把最初由 pozs 写的答案贴在评论区。

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 +)

例子 :

select * from json_array_elements('[1,true, [2,false]]')

输出值

 -------------
| 1         |
-------------
| true      |
-------------
| [2,false] |
-------------

这里 可以找到 v9.4的文档。

小开

更难的例子:

假设您有一个表，其中每个行都包含 jsonb 数组，您希望将所有这些数组拆分(或取消嵌套) ，并对其中包含的记录进行一些聚合计算。

表(设为 categories) :

 id | specifics (jsonb)
-----------------------------------------------------------------------------------
1 | [{"name": "Brand", "required": true}, {"name": "Color", "required": false}]
2 | [{"name": "Brand", "required": false}, {"name": "Color", "required": false}]

因此，如果你想统计，你有多少必需的细节，你将需要使用这样的查询:

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;

这里的 FROM x, function(x.column)是 侧向连接的缩写形式，它有效地将来自 categories的每一行与来自 jsonb 数组的 jsonb_to_recordset函数创建的虚表连接在同一行中。

结果就是:

 name  | total
---------------
Brand |     1

链接到 DB Fiddle: https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0


提交答案