CODEWITHSUNDEEP
postgresqlspring-data-jpajsonb
Vishal Gajera
Vishal Gajera

Reputation: 4207

Postgres JSONB datatype - How to extract data from JSON (of type JsonB) field of postgres database?

Hello Friends, I need a help to solve the following issue,

I have set of record into my postgres db table, where table has JSONB type field.

JSONB type column contains following JSON,

Record#1 :-

{
  "key1": "value1",
  "key2": "value2",
  "audience": [
    {
      "name": "Person1",
      "email": "[email protected]",
      "country": "UK",
      "primaryNumber": "+1234567890",
      "secondaryNumber": "+1234567890"
    },
    {
      "name": "Person2",
      "email": "[email protected]",
      "country": "UK",
      "primaryNumber": "+1234567890",
      "secondaryNumber": "+1234567890"
    }
  ]
}

Record#2:-

{
  "key1": "value1",
  "key2": "value2",
  "audience": [
    {
      "name": "Person3",
      "email": "[email protected]",
      "country": "UK",
      "primaryNumber": "+1234567890",
      "secondaryNumber": "+1234567890"
    },
    {
      "name": "Person4",
      "email": "[email protected]",
      "country": "UK",
      "primaryNumber": "+1234567890",
      "secondaryNumber": "+1234567890"
    }
  ]
}

Expected Result (Get All Audience) :- 

[
  {
    "name": "Person1",
    "email": "[email protected]",
    "country": "UK",
    "primaryNumber": "+1234567890",
    "secondaryNumber": "+1234567890"
  },
  {
    "name": "Person2",
    "email": "[email protected]",
    "country": "UK",
    "primaryNumber": "+1234567890",
    "secondaryNumber": "+1234567890"
  },
  {
    "name": "Person3",
    "email": "[email protected]",
    "country": "UK",
    "primaryNumber": "+1234567890",
    "secondaryNumber": "+1234567890"
  },
  {
    "name": "Person4",
    "email": "[email protected]",
    "country": "UK",
    "primaryNumber": "+1234567890",
    "secondaryNumber": "+1234567890"
  }
]

Can Anyone help me to design a query either native query or through spring-data-jpa ?

I appreciate really if anyone who can help me to carry out from this situation!

Upvotes: 0

Views: 533

Answers (1)

klin
klin

Reputation: 121909

You should extract 'audience' array elements of each row with jsonb_array_elements() and aggregate them to a single json object with jsonb_agg():

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

Working example in rextester.

Upvotes: 2

Related Questions