Postgres JSONB编辑列

Question

我正在阅读Postgres Jsonb文档，但找不到解决我遇到的一个小问题的方法。

我有一张桌子：MY_TABLE

具有以下列：

用户、名称、数据和购买

需要注意的是，“数据”是一个jsonb，有多个字段。“Data”中的一个字段是“Attribute”，但它可以保存的值不同步。我的意思是，它可以是一个字符串，一个字符串列表，一个空列表，或者只是一个空字符串。然而，我想改变这一点。

我希望允许的唯一值是字符串列表和空列表。我想将所有空字符串转换为空列表，将常规字符串转换为字符串列表。

我曾尝试使用json\u build\u数组，但没有任何运气

例如，我希望我的最后一个jsonb看起来像：

       [{
           "Id": 1,
           "Attributes": ["Test"]

       },
       {
           "Id": 2,
           "Attributes": []

       },
{
           "Id": 3,
           "Attributes": []

       }]

从转换时

    {
        "Id": 1,
        "Attributes": "Test"

    },
{
           "Id": 2,
           "Attributes": ""

       },
{
           "Id": 3,
           "Attributes": []

       }
]

我只关心Json中的“Attributes”字段，而不关心任何其他字段。

我还想确保一些属性有一个空字符串"属性": "", 他们被映射到一个空列表，而不是一个空字符串列表（[]不["]）

我还想为已经持有空数组值的属性保留空数组值（[]）。

这是我到目前为止得到的：

jsonb_set(
    mycol,
    '{Attributes}',
    case when js ->> 'Attributes' <> '' 
        then jsonb_build_array(js ->> 'Attributes')
        else '[]'::jsonb
    end
)

但是，属性：[]正在映射到[“[]”

Answer 1

使用jsonb\u array\u elements（）迭代每个“数据”单元的元素，并使用jsonb\u agg将变换值重新组合到一个数组中：

WITH test_data(js) AS (
    VALUES ($$ [
      {
        "Id": 1,
        "Attributes": "Test"
      },
      {
        "Id": 2,
        "Attributes": ""
      },
      {
        "Id": 3,
        "Attributes": []
      }
    ]
    $$::JSONB)
)
SELECT transformed_elem
FROM test_data
JOIN LATERAL (
    SELECT jsonb_agg(jsonb_set(
            elem,
            '{Attributes}',
            CASE
                WHEN elem -> 'Attributes' IN ('""', '[]')          THEN '[]'::JSONB
                WHEN jsonb_typeof(elem -> 'Attributes') = 'string' THEN jsonb_build_array(elem -> 'Attributes')
                ELSE elem -> 'Attributes'
            END
        )) AS transformed_elem
    FROM jsonb_array_elements(test_data.js) AS f(elem) -- iterate over every element in the array
) s
 ON TRUE

退货

[{"Id": 1, "Attributes": ["Test"]}, {"Id": 2, "Attributes": []}, {"Id": 3, "Attributes": []}]