PostgreSQL-向JSONB数组的每个对象添加密钥 [英] PostgreSQL - Add key to each objects of an JSONB array

问题描述

我的数据库包含一个表，该表的列为 jsonb类型，我想使用来自PostgreSQL的函数/运算符更新这些数据的一部分.鉴于我们有这个:

My database contains a table which has a column with jsonb type, and I want to update a part of these data using functions/operators from postgreSQL. Given we have this:

{
  "A":[
    {"index":"1"},
    {"index":"2"}
  ],
  "B":[
    {"index":"3"},
    {"index":"4"}
  ]
}

假设我们要向"A"数组中的对象添加具有空数组的键，以便:

Let's say we went to add a key with an empty array to objects from "A" array, in order to have:

{
  "A":[
    {"index":"1", "myArray":[]},
    {"index":"2", "myArray":[]}
  ],
  "B":[
    {"index":"3"},
    {"index":"4"}
  ]
}

我该如何进行?

我已经尝试过这种事情，但没有成功:

I've already tried this kind of things without success:

UPDATE myTable SET myColumn = (myColumn::jsonb)->>'A' || '{"myArray":[]}'

---

UPDATE myTable SET myColumn = (
  SELECT jsonb_agg(jsonb_set(
    element, 
    array['A'], 
    to_jsonb(((element ->> 'A')::jsonb || '{"myArray":[]}')::jsonb)
  ))
  FROM jsonb_array_elements(myColumn::jsonb) element
)::json

---

UPDATE myTable SET myColumn = (
  SELECT jsonb_each((element ->> 'A')::jsonb) || '{"myArray":[]}'::jsonb
  FROM jsonb_array_elements(myColumn::jsonb) element
)::json

很显然，所有这些测试都是很大的失败.我很难理解postgreSQL函数的工作方式.

Obviously, all of these tests have been big failure. I have difficulties to understand how works postgreSQL functions.

有人可以帮忙吗?

推荐答案

使用jsonb_array_elements和jsonb_set的方法是正确的想法，但是以某种方式将它们嵌套在错误的位置:

The approach with jsonb_array_elements and jsonb_set was the right idea, but somehow you nested them the wrong way round:

UPDATE myTable SET myColumn = jsonb_set(myColumn, '{A}', (
  SELECT jsonb_agg( element || '{"myArray":[]}' )
  FROM jsonb_array_elements(myColumn -> 'A') element
));

^{(在线演示)}

顺便说一句，如果您的列已经具有jsonb数据类型，则不需要任何强制转换.

Btw if your column already has jsonb data type, you shouldn't need any casts.

这篇关于PostgreSQL-向JSONB数组的每个对象添加密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！