转置 JSONB 数组元素 - Postgres 11.5?

Question

我有一个表，结构定义如下

CREATE TABLE logs (
    logid uuid NOT NULL,
    logjson jsonb NOT NULL,
    createdtime timestamp NOT NULL
);

示例 JSONB 字符串如下所示

{
    "RouteId": "353f89f5-b414-40cd-bd19-20378d51e723",
    "RouteName": "GetPickCompleteMessages",
    "CustomLinks": {
    },
    "MachineName": "12ws3",
    "DataBaseTimings": [
        {
            "ApiMethod": null,
            "ExecuteType": "OpenAsync",
            "CommandString": "Connection OpenAsync()",
            "DurationMilliseconds": 1138.5,
        },
        {
            "ApiMethod": null,
            "ExecuteType": "Reader",
            "CommandString": "GetPckg",
            "DurationMilliseconds": 124.5,
        },
            {
            "ApiMethod": null,
            "ExecuteType": "Close",
            "CommandString": "Connection Close()",
            "DurationMilliseconds": 27.9,
        }
    ],
    "DurationMilliseconds": 1546.2
}

我正在寻找以这种方式输出

| RouteName                 | OpenAsyncTime | ReaderTime | CloseTime | TotalDuration |
--------------------------------------------------------------------------------------
| GetPickCompleteMessages   |    1138.5     | 124.5      | 27.9      |  1546.2       |
--------------------------------------------------------------------------------------

非常感谢对此的任何指示。

另外，如果有多个阵列，我该如何扩展解决方案。通过多个数组，这就是我的意思

{
    "RouteId": "353f89f5-b414-40cd-bd19-20378d51e723",
    "RouteName": "GetPickCompleteMessages",
    "CustomLinks": {
    },
    "MachineName": "12ws3",
    "ChildProfiles": [
        {
            "ApiMethod": "PublishShipViaRequestToQueue",
            "DurationMilliseconds": 0.1,
        }
    ],
    "DataBaseTimings": [
        {
            "ApiMethod": null,
            "ExecuteType": "OpenAsync",
            "CommandString": "Connection OpenAsync()",
            "DurationMilliseconds": 1138.5,
        },
        {
            "ApiMethod": null,
            "ExecuteType": "Reader",
            "CommandString": "GetPckg",
            "DurationMilliseconds": 124.5,
        },
            {
            "ApiMethod": null,
            "ExecuteType": "Close",
            "CommandString": "Connection Close()",
            "DurationMilliseconds": 27.9,
        }
    ],
    "DurationMilliseconds": 1546.3
}

输出需要以这种方式

| RouteName                          | PublishShipViaRequestToQueueTime | OpenAsyncTime | ReaderTime | CloseTime | TotalDuration |
-----------------------------------------------------------------------------------------------------------------------------------
| GenerateRequestAndPublishToQueue   |    0.1                           | 1138.5        | 124.5       | 27.9      |  1546.3       |
------------------------------------------------------------------------------------------------------------------------------------

提前感谢你

Answer 1

我认为一些json访问应该这样做：

SELECT
   logjson -> 'RouteName' AS "RouteName",
   logjson -> 'DataBaseTimings' -> 0 -> 'DurationMilliseconds' AS "OpenAsyncTime",
   logjson -> 'DataBaseTimings' -> 1 -> 'DurationMilliseconds' AS "ReaderTime",
   logjson -> 'DataBaseTimings' -> 2 -> 'DurationMilliseconds' AS "CloseTime",
   logjson -> 'DurationMilliseconds' AS "TotalDuration"
FROM
   logs

如果由于索引未知而无法解决问题，则必须对 json 数组执行子查询：

SELECT
   logjson -> 'RouteName' AS "RouteName",
   (SELECT el -> 'DurationMilliseconds'
       FROM jsonb_array_elements(logjson -> 'DataBaseTimings') el
       WHERE el ->> 'ExecuteType' = 'OpenAsync'
   ) AS "OpenAsyncTime",
   (SELECT el -> 'DurationMilliseconds'
       FROM jsonb_array_elements(logjson -> 'DataBaseTimings') el
       WHERE el ->> 'ExecuteType' = 'Reader'
   ) AS "ReaderTime",
   (SELECT el -> 'DurationMilliseconds'
       FROM jsonb_array_elements(logjson -> 'DataBaseTimings') el
       WHERE el ->> 'ExecuteType' = 'Close'
   ) AS "CloseTime",
   logjson -> 'DurationMilliseconds' AS "TotalDuration"
FROM
   logs 

或者用花哨的方式去做并使用

SELECT
   logjson -> 'RouteName' AS "RouteName",
   typed_values -> 'OpenAsync' AS "OpenAsyncTime",
   typed_values -> 'Reader' AS "ReaderTime",
   typed_values -> 'Close' AS "CloseTime",
   logjson -> 'DurationMilliseconds' AS "TotalDuration"
FROM
   logs,
   LATERAL (
       SELECT jsonb_object_agg(el ->> 'ExecuteType', el -> 'DurationMilliseconds') AS typed_values
       FROM jsonb_array_elements(logjson -> 'DataBaseTimings') el
   ) AS t