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

Question

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!

this could help: postgresql.org/docs/current/static/functions-json.html — Tryph
– Tryph, Commented Sep 5, 2018 at 10:43
@Tryph I tried to solve it first. but looks I am unable to decide such a query as per the expectation. that's why I request openly who can help me. Thanks for your response. — Vishal Gajera
– Vishal Gajera, Commented Sep 5, 2018 at 10:46

klin · Accepted Answer · 2018-09-05 10:46:57Z

2

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.

answered Sep 5, 2018 at 10:46

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related