postgresql - Postgres JSONB 数据类型 - 如何从 postgres 数据库的 JSON（JsonB 类型）字段中提取数据？

Question

您好朋友， 我需要帮助来解决以下问题，

我在我的 postgres 数据库表中设置了记录，其中表具有JSONB类型字段。

JSONB类型列包含以下内容JSON，

记录#1：-

{
  "key1": "value1",
  "key2": "value2",
  "audience": [
    {
      "name": "Person1",
      "email": "[e-mail]",
      "country": "UK",
      "primaryNumber": "[phone]",
      "secondaryNumber": "[phone]"
    },
    {
      "name": "Person2",
      "email": "[e-mail]",
      "country": "UK",
      "primaryNumber": "[phone]",
      "secondaryNumber": "[phone]"
    }
  ]
}

记录#2：-

{
  "key1": "value1",
  "key2": "value2",
  "audience": [
    {
      "name": "Person3",
      "email": "[e-mail]",
      "country": "UK",
      "primaryNumber": "[phone]",
      "secondaryNumber": "[phone]"
    },
    {
      "name": "Person4",
      "email": "[e-mail]",
      "country": "UK",
      "primaryNumber": "[phone]",
      "secondaryNumber": "[phone]"
    }
  ]
}

预期结果（吸引所有观众）：-

[
  {
    "name": "Person1",
    "email": "[e-mail]",
    "country": "UK",
    "primaryNumber": "[phone]",
    "secondaryNumber": "[phone]"
  },
  {
    "name": "Person2",
    "email": "[e-mail]",
    "country": "UK",
    "primaryNumber": "[phone]",
    "secondaryNumber": "[phone]"
  },
  {
    "name": "Person3",
    "email": "[e-mail]",
    "country": "UK",
    "primaryNumber": "[phone]",
    "secondaryNumber": "[phone]"
  },
  {
    "name": "Person4",
    "email": "[e-mail]",
    "country": "UK",
    "primaryNumber": "[phone]",
    "secondaryNumber": "[phone]"
  }
]

任何人都可以帮我设计一个查询吗native query？spring-data-jpa

如果有人可以帮助我摆脱这种情况，我真的很感激！

标签： postgresqlspring-data-jpajsonb

Answer 1

您应该提取'audience'每行的数组元素jsonb_array_elements()并将它们聚合到单个 json 对象中jsonb_agg()：

select jsonb_agg(value)
from my_table
cross join jsonb_array_elements(json_data->'audience')

rextester 中的工作示例。