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
    select   '{"oranges":[{"id":85744212,"xName":"pop"},{"id":85744213,"xName":"ziq"}]}'::jsonb hdata

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.