将元素添加到 Postgres 中的 JSON 对象 [英] Add element to JSON object in Postgres

问题描述

我在数据库(postgres 9.2.1)中有一个文本字段，其中有一个 json blob.显然，它看起来与此类似，但都在一行中:

I have a text field in a database (postgres 9.2.1) with a json blob in it. It looks something similar to this except all on a single line, obviously:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

我需要向 json 数组添加一个元素，使其看起来像这样:

I need to add an element to json array so that it looks like this:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "something_new": {
    "checked": "1",
    "label": "Something New"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

我不太关心新数组元素的位置.它可能在agency_name 之后.有没有一种简单的方法可以在 postgres 中做到这一点?

I'm not as concerned about the placement of the new array element. It could be after agency_name. Is there an easy way to do this in postgres?

推荐答案

即使我遇到了同样的问题，我也想动态地向 jsonb[] 添加新元素.

Even I had the same problem, I wanted to dynamically append new elements to jsonb[].

假设 column_jsonb[] = [{"name":"xyz","age":"12"}]

Assume column_jsonb[] = [{"name":"xyz","age":"12"}]

UPDATE table_name
   SET column_jsonb[] = array_append(column_jsonb[],'{"name":"abc","age":"22"}');

结果:[{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]

Result : [{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]

这篇关于将元素添加到 Postgres 中的 JSON 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！