更新JSONB列，其中JSONB列具有特定值postgreSQL [英] Update JSONB column where JSONB column has certain value postgreSQL

问题描述

我在Postgres DB中有一个名为guest_group的表，以及一个具有以下值的名为custom_fields的列:(示例)

I have a table named guest_group in a Postgres DB and a column named custom_fields with the following values: (example)

[
   {
      "value":"Frau Barbara Test",
      "display_name":"Name",
      "servicio_tags":[
         "full-name"
      ]
   },
   {
      "value":"118",
      "display_name":"Zimmernummer",
      "servicio_tags":[
         "room-number"
      ]
   },
   {
      "value":"likes postgreSQL",
      "display_name":"Traces",
      "servicio_tags":[
         "trace"
      ]
   }
]

我想用

• value等于Frau Barbara Test，并且servicio_tags数组等于["full-name"]
• 加号，其中value等于118，其中servicio_tags数组等于["room-number"]

• value equals to Frau Barbara Test and where the servicio_tags array is equals to ["full-name"]
• plus where the value equal to 118 where the servicio_tags array is equal to ["room-number"]

更新应将新对象添加到JSONB:

The Update should add a new object to the JSONB:

{
   "value":"likes JSONB",
   "display_name":"Traces",
   "servicio_tags":[
      "trace"
   ]
}

到目前为止，我的尝试:

My try so far:

UPDATE guest_group 
SET custom_fields = 
jsonb_insert('[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]'::jsonb,
'{0}', 
'{"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}'::jsonb, 
true)
 where custom_fields::text like '%"Frau Barbara Test"%'
 and custom_fields::text like '%"118 "%'

此尝试将特定行的列中的值更改为以下内容:

This try changes the value in column of the specific row to the following:

[  
   {  
      "value":"Frau Barbara Test",
      "display_name":"Name",
      "servicio_tags":[  
         "full-name"
      ]
   },
   {  
      "value":"likes JSONB",
      "display_name":"Traces",
      "servicio_tags":[  
         "trace"
      ]
   },
   {  
      "value":"118",
      "display_name":"Zimmernummer",
      "servicio_tags":[  
         "room-number"
      ]
   }
]

所以:

   {
      "value":"likes postgreSQL",
      "display_name":"Traces",
      "servicio_tags":[
         "trace"
      ]
   }

迷路了.如何在不丢失此数据的情况下进行更新?

gets lost. How can do the update without loosing this data?

推荐答案

我建议不要将JSON作为字符串进行比较.而是使用大量JSON运算符和函数.要查找包含某个值的Tests夫人的行，例如，可以使用@>运算符:

I would recommend not to compare JSON as strings. Instead, use the vast array of JSON operators and functions. To find rows that contain Mrs. Test in a value, you could for example use the @> operator:

WHERE '[{"value": "Frau Barbara Test"}]'::jsonb <@ custom_fields

另请参见 JSONB字段中的对象的Postgresql查询数组.

但是，这尚未检查它是否具有full-name标签，因此我们可以尝试

However, this doesn't yet check that it's the same object that has a full-name tag, so we might try

WHERE '[{"value": "Frau Barbara Test", "servicio_tags": ["full-name"]}]'::jsonb <@ custom_fields

这比文本比较要好得多，但仍然不完全符合您的要求.由于@>运算符的语义，它会检查标签数组以包含一个"full-name"字符串，而不是将其作为单个元素.

That's much better than text comparison, but still not exactly what you asked for. Due to the semantics of the @> operator, it checks the tags array to contain a "full-name" string, not to have it as its single element.

要做到这一点，它变得更加复杂，涉及到jsonb_array_elements上的子查询并显式访问其json组件以进行比较:

To do that, it gets a bit more complicated, involving a subquery on the jsonb_array_elements and accessing its json components explicitly for comparison:

SELECT *
FROM guest_group
WHERE EXISTS(
  SELECT 1
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->>'value' = 'Frau Barbara Test'
    AND el->'servicio_tags' = '["full-name"]'::jsonb)

您还可以使用ANY混合两种方法:

You could also mix the two approaches using ANY:

SELECT *
FROM guest_group
WHERE '{"value": "Frau Barbara Test"}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["full-name"]'::jsonb)

(在线演示)

现在配备了这些工具，您可以添加第二个条件来匹配房间号.然后使用

Now equipped with these tools, you can add a second condition to match the room number. Then use

UPDATE guest_group 
SET custom_fields = jsonb_insert(custom_fields, '{0}', '{"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}'::jsonb, true)
WHERE …

这篇关于更新JSONB列，其中JSONB列具有特定值postgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！