在JSONB数组中的每个对象中如何添加新的键值对 – PostgreSQL

huangapple go评论83阅读模式
英文:

How to add new key-value pair in each object of JSONB Array- PostgreSQL

问题

以下是翻译好的部分:

我在我的PostgreSQL数据库中有一张名为master_data_approval_table的表,其中有一个字段approval_value(类型为Jsonb),其中包含以下JSON结构:

[{
    "name": "abc",
    "email": "abc.pqr@gmail.com"
},
{
    "name": "xyz",
    "email": "xyz.pqr@gmail.com"
}]

现在我们想要在数组中的每个对象中添加一个键值对,即**"comment": null**,就像下面的示例一样:

[{
    "name": "abc",
    "email": "abc.pqr@gmail.com",
    "comment": null
},
{
    "name": "xyz",
    "email": "xyz.pqr@gmail.com",
    "comment": null
}]

此外,我们需要对表中的所有记录执行此操作。我已尝试不同的现有答案和查询,但没有帮助,因此请有人指导我如何在PostgreSQL中为上述情况编写查询。

提前致谢。

英文:

I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below JSON structure:

[{
		"name": "abc",
		"email": "abc.pqr@gmail.com"
	},
	{
		"name": "xyz",
		"email": "xyz.pqr@gmail.com"
	}
]

Now we want to add one more key-value pair i.e. "comment": null in each object of the array like the below one:

[{
		"name": "abc",
		"email": "abc.pqr@gmail.com",
		"comment": null
	},
	{
		"name": "xyz",
		"email": "xyz.pqr@gmail.com",
		"comment": null
	}
]

In more to that we need to do this for all the records of the table.
I have tried different existing answers and queries but no help so please somebody guide me on how to write a query in PostgreSQL for the above scenario.

Thanks in advance.

答案1

得分: 1

你可以使用 jsonb_array_elements() 来展开 JSON 数组,使用 || 来修改每个对象以添加新的键/值对,最后使用 jsonb_agg() 来聚合回原始的顺序:

select t.approval_value, e.new_approval_value
from master_data_approval_table t
cross join lateral (
    select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
) e

如果你想实际修改列中的值,你可以使用 update:

update master_data_approval_table t
set approval_value = (
    select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
)

fiddle

英文:

You can unnest the JSON array with jsonb_array_elements(), use || to modify each object to add the new key/value, and finally aggregate back with jsonb_agg(), with respect to the original ordering:

select t.approval_value, e.new_approval_value
from master_data_approval_table t
cross join lateral (
    select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
) e
approval_value new_approval_value
[<br>    {<br>        "name": "abc",<br>        "email": "abc.pqr@gmail.com"<br>    },<br>    {<br>        "name": "xyz",<br>        "email": "xyz.pqr@gmail.com"<br>    }<br>] [<br>    {<br>        "name": "abc",<br>        "email": "abc.pqr@gmail.com",<br>        "comment": null<br>    },<br>    {<br>        "name": "xyz",<br>        "email": "xyz.pqr@gmail.com",<br>        "comment": null<br>    }<br>]

If you wanted to actually modify the values in the column, you would update:

update master_data_approval_table t
set approval_value = (
    select jsonb_agg( e.elt || &#39;{&quot;comment&quot;: null}&#39; order by e.seq) new_approval_value
    from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
)

fiddle

huangapple
  • 本文由 发表于 2023年7月6日 16:13:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76626794.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定