如何在PostgreSQL中更新json(其中一个条目是list)

6yjfywim 于 2023-05-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(267)

我是新的Postgres JSON，我尝试了一些quires，但它不工作。我有一个JSON在下面的格式保存在DB中。现在我必须编写一个更新查询（这将在迁移过程中执行）来更新值，如下所示。
1.如果交换机具有displayName =“Name”，则必须将其替换为ProductName。
1.应在第二个索引中的switchButtons中添加新条目（例如）。入口是
{“flex”：4，“name”：“wwn”，“工具提示”：true，“isStatic”：false，“selected”：true，“sortable”：true，“可过滤”：true，“displayName”：“WWN”}
保存在DB中的主JSON：

{
  "common": {
    "rightData": []
  },
  "tableCache": {
    "switches": [
      {
        "flex": 4,
        "name": "name",
        "route": true,
        "tooltip": true,
        "isStatic": true,
        "selected": true,
        "sortable": true,
        "sortIndex": 0,
        "sortOrder": "ASC",
        "filterable": true,
        "displayName": "Name"
      },
      {
        "flex": 4,
        "name": "type",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Type"
      },
      {
        "flex": 4,
        "name": "logicalSwitchType",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Logical Rate"
      }
    ],
    "switchButtons": [
      {
        "flex": 4,
        "name": "name",
        "route": true,
        "tooltip": true,
        "isStatic": true,
        "selected": true,
        "sortable": true,
        "sortIndex": 0,
        "sortOrder": "ASC",
        "filterable": true,
        "displayName": "Name"
      },
      {
        "flex": 4,
        "name": "type",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Type"
      }
    ]
  }
}

预期结果：

{
  "common": {
    "rightData": []
  },
  "tableCache": {
    "switches": [
      {
        "flex": 4,
        "name": "name",
        "route": true,
        "tooltip": true,
        "isStatic": true,
        "selected": true,
        "sortable": true,
        "sortIndex": 0,
        "sortOrder": "ASC",
        "filterable": true,
        **"displayName": "ProductName"**
      },
      {
        "flex": 4,
        "name": "type",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Type"
      },
      {
        "flex": 4,
        "name": "logicalSwitchType",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Logical Rate"
      }
    ],
    "switchButtons": [
      {
        "flex": 4,
        "name": "name",
        "route": true,
        "tooltip": true,
        "isStatic": true,
        "selected": true,
        "sortable": true,
        "sortIndex": 0,
        "sortOrder": "ASC",
        "filterable": true,
        "displayName": "Name"
      },
    **{
        "flex": 4,
        "name": "wwn",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "WWN"
      }**
      {
        "flex": 4,
        "name": "type",
        "tooltip": true,
        "isStatic": false,
        "selected": true,
        "sortable": true,
        "filterable": true,
        "displayName": "Type"
      }
    ]
  }
}

postgresql

来源：https://stackoverflow.com/questions/76287962/how-to-update-jsonone-of-the-entry-is-list-in-postgresql

1条答案

按热度按时间

qv7cva1a1#

WITH updated_switches AS(
    SELECT id, json_agg(
        CASE WHEN element->>'displayName' = 'Name' THEN element || jsonb_build_object('displayName', 'WWN', 'name', 'wnn') ELSE element END
    ) res
    FROM your_table
        CROSS JOIN jsonb_path_query(json_field::jsonb, '$[*].tableCache.switches') switches
        CROSS JOIN jsonb_array_elements(switches) element
    GROUP BY id
)
UPDATE your_table SET json_field = (json_field::jsonb) || jsonb_build_object('tableCache', jsonb_build_object('switches', res))
FROM updated_switches
WHERE your_table.id = updated_switches.id;

赞(0）回复(0）举报 2023-05-22

我来回答

如何在PostgreSQL中更新json(其中一个条目是list)

1条答案

相关问题

热门标签

最新问答