如何将json数组转换为postgres中的行 [英] How to turn a json array into rows in postgres

问题描述

我的 postgres 数据库中存储了一个 json 数组.json 如下所示:

I have a json array stored in my postgres database. The json looks like this:

[
    {
        "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 语句必须以这种方式返回数据:

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:

 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 类型.

推荐答案

我在评论部分发布了最初由 pozs 撰写的答案.

I post the answer originally written by pozs in the comment section.

unnest() 用于 PostgreSQL 的数组类型.

unnest() is for PostgreSQL's array types.

可以使用以下函数之一:

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

示例:

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

输出值

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

此处 v9.4 的文档可以在此处找到了.

Here where the documentation for v9.4 can be found.

这篇关于如何将json数组转换为postgres中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！