时间戳

首页 > 解决方案 > 如何从 jsonb 对象数组中提取值？

arrays - 如何从 jsonb 对象数组中提取值？

问题描述

我正在使用 Postgres，我试图获取数组中具有键 statusCode 的所有元素，并将值放入数组中，以便我可以显示它。

"systemStatuses": [
        {
            "changedBy": "monsjenni557",
            "timeStamp": 1554151540.9612856,
            "statusCode": "S01",
            "statusDescription": "Received"
        },
        {
            "changedBy": "monsjenni557",
            "timeStamp": 1554151546.2600567,
            "statusCode": "SF02",
            "statusDescription": "Request Validation Fail"
        }
    ]

结果应该是["S01","SF02"]

标签： arrayspostgresqljsonb

解决方案

您可以展开然后聚合回 jsonb 字段。该WITH部分仅用于示例目的。

WITH exampleData AS (
    SELECT '[
        {
            "changedBy": "monsjenni557",
            "timeStamp": 1554151540.9612856,
            "statusCode": "S01",
            "statusDescription": "Received"
        },
        {
            "changedBy": "monsjenni557",
            "timeStamp": 1554151546.2600567,
            "statusCode": "SF02",
            "statusDescription": "Request Validation Fail"
        }
    ]'::jsonb as regionSales
)

SELECT (
    SELECT jsonb_agg( sale -> 'statusCode')
    FROM jsonb_array_elements(regionSales) AS sale
)

FROM exampleData

推荐阅读