是否有具有魔术功能可以提取所有选择的键/嵌套键，包括JSONB的数组

Question

给定了一个JSONB和一组键

我已经尝试提取键值并分配给文本[]，然后使用jsonb_object(text [](。它运行良好，但是问题到了，当键带有一系列JSON时。

create table my_jsonb_table 
(
data_col jsonb
);
insert into my_jsonb_table (data_col) Values ('{
    "schemaVersion": "1",
    "Id": "20180601550002",
    "Domains": [
        {
            "UID": "29aa2923",
            "quantity": 1,
            "item": "book",
            "DepartmentDomain": {
                "type": "paper",
                "departId": "10"
            },
            "PriceDomain": {
                "Price": 79.00,
                "taxA": 6.500,
                "discount": 0
            }
        },
        {
            "UID": "bbaa2923",
            "quantity": 2,
            "item": "pencil",
            "DepartmentDomain": {
                "type": "wood",
                "departId": "11"
            },
            "PriceDomain": {
                "Price": 7.00,
                "taxA": 1.5175,
                "discount": 1
            }
        }
    ],
    "finalPrice": {
        "totalTax": 13.50,
        "total": 85.0
    },
    "MetaData": {
        "shopId": "1405596346",
        "locId": "95014",
        "countryId": "USA",
        "regId": "255",
        "Date": "20180601"
    }
}
')

这是我要实现的目标：

SELECT some_magic_fun(data_col,'Id,Domains.UID,Domains.DepartmentDomain.departId,finalPrice.total')::jsonb FROM my_jsonb_table;

我正在尝试创建该魔术功能，该功能以JSONB格式提取给定的键，到目前为止，我能够提取标量项目并将其放入文本[]并使用JSONB_OBJECT中。但是不知道如何提取数组的所有元素

预期输出：

 {
    "Id": "20180601550002",
    "Domains": [
        {
            "UID": "29aa2923",
            "DepartmentDomain": {
             "departId": "10"
            }
        },
        {
            "UID": "bbaa2923",
            "DepartmentDomain": {
                "departId": "11"
            }
        }
    ],
    "finalPrice": {
        "total": 85.0
    }
}

Answer 1

我不知道任何魔术。您必须自己重建。

select jsonb_build_object(
    -- Straight forward
    'Id', data_col->'Id',
    'Domains', (
        -- Aggregate all the "rows" back together into an array.
        select jsonb_agg(
            -- Turn each array element into a new object
            jsonb_build_object(
                'UID', domain->'UID',
                'DepartmentDomain', jsonb_build_object(
                    'departId', domain#>'{DepartmentDomain,departId}'
                )
            )
        )
        -- Turn each element of the Domains array into a row
        from jsonb_array_elements( data_col->'Domains' ) d(domain)
    ),
    -- Also pretty straightforward
    'finalPrice', jsonb_build_object(
        'total', data_col#>'{finalPrice,total}'
    )
) from my_jsonb_table;

这可能不是JSON列的好使用。您的数据是关系的，可以更好地符合传统的关系表。