1

I am trying to produce a specific json object output from rows of a single column of jsonb and I'm stuck. Before I drop into PL/pgSQL, I wanted to ask the question here. I need to concatenate a column of JSONB into a single JSON object rather than an array.

Here's an example structure of the data I need to process (cte is data is a format I don't control):

with cte as (
    select   '{"apples":[{"id":25711224,"xName":"abc"},{"id":25711225,"xName":"yzx"}]}'::jsonb hdata
        union   
    select   '{"oranges":[{"id":85744212,"xName":"pop"},{"id":85744213,"xName":"ziq"}]}'::jsonb hdata
)
select 
    hdata
from 
    cte

Which looks like: enter image description here

I want to make this JSON:

{
  "oranges": [
    {
      "id": 85744212,
      "xName": "pop"
    },
    {
      "id": 85744213,
      "xName": "ziq"
    }
  ],
  "apples": [
    {
      "id": 25711224,
      "xName": "abc"
    },
    {
      "id": 25711225,
      "xName": "yzx"
    }
  ]
}

The example has 2 rows, but I could recieve any number of rows. But I know the property name of each row (apples/oranges) will be unique, so it is really a concatenation that I want:

select '{"oranges":[{"id":85744212,"xName":"pop"},{"id":85744213,"xName":"ziq"}]}'::jsonb||'{"apples":[{"id":25711224,"xName":"abc"},{"id":25711225,"xName":"yzx"}]}'::jsonb

I've tried various groupings w/ aggregate functions, which gets me close. But not quite what I need.

  • 1
    select jsonb_object_agg(k, hdata->k) from cte, lateral (select * from jsonb_object_keys(hdata)) as top_key(k); – Abelisto Dec 2 '16 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.