如何将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 look 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语句必须以这种方式返回数据:

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屋！