Postgres Build Complex JSON Object from Wide Column Like Design to Key Value

Question

I could really use some help here before my mind explodes... Given the following data structure:

SELECT * FROM (VALUES (1, 1, 1, 1), (2, 2, 2, 2)) AS t(day, apple, banana, orange);

 day | apple | banana | orange 
-----+-------+--------+--------
   1 |     1 |      1 |      1
   2 |     2 |      2 |      2

I want to construct a JSON object which looks like the following:

{
  "data": [
    {
      "day": 1,
      "fruits": [
        {
          "key": "apple",
          "value": 1
        },
        {
          "key": "banana",
          "value": 1
        },
        {
          "key": "orange",
          "value": 1
        }
      ]
    }
  ]
}

Maybe I am not so far away from my goal:

SELECT json_build_object(
  'data', json_agg(
    json_build_object(
      'day', t.day,
      'fruits', t)
    )
) FROM (VALUES (1, 1, 1, 1), (2, 2, 2, 2)) AS t(day, apple, banana, orange);

Results in:

{
  "data": [
    {
      "day": 1,
      "fruits": {
        "day": 1,
        "apple": 1,
        "banana": 1,
        "orange": 1
      }
    }
  ]
}

I know that there is json_each which may do the trick. But I am struggling to apply it to the query.

Edit: This is my updated query which, I guess, is pretty close. I have dropped the thought to solve it with json_each. Now I only have to return an array of fruits instead appending to the fruits object:

SELECT json_build_object(
    'data', json_agg(
        json_build_object(
            'day', t.day,
            'fruits', json_build_object(
                'key', 'apple', 
                'value', t.apple, 
                'key', 'banana', 
                'value', t.banana, 
                'key', 'orange', 
                'value', t.orange
            )
        )
    )
) FROM (VALUES (1, 1, 1, 1), (2, 2, 2, 2)) AS t(day, apple, banana, orange);

Would I need to add a subquery to prevent a nested aggregate function?

klin · Accepted Answer · 2017-11-07 10:36:49Z

4

Use the function jsonb_each() to get pairs (key, value), so you do not have to know the number of columns and their names to get a proper output:

select jsonb_build_object('data', jsonb_agg(to_jsonb(s) order by day))
from (
    select day, jsonb_agg(jsonb_build_object('key', key, 'value', value)) as fruits
    from (
        values (1, 1, 1, 1), (2, 2, 2, 2)
    ) as t(day, apple, banana, orange),
    jsonb_each(to_jsonb(t)- 'day')
    group by 1
    ) s;

The above query gives this object:

{
    "data": [
        {
            "day": 1,
            "fruits": [
                {
                    "key": "apple",
                    "value": 1
                },
                {
                    "key": "banana",
                    "value": 1
                },
                {
                    "key": "orange",
                    "value": 1
                }
            ]
        },
        {
            "day": 2,
            "fruits": [
                {
                    "key": "apple",
                    "value": 2
                },
                {
                    "key": "banana",
                    "value": 2
                },
                {
                    "key": "orange",
                    "value": 2
                }
            ]
        }
    ]
}

answered Nov 7, 2017 at 10:36

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Yannic Hamann Over a year ago

Thanks for your help. That's awesome work. Could you explain the reason for the minus after to_jsonb(t)?

Michel Milezzi Over a year ago

He's removing key "day" from jsonb returned by to_jsonb(t), since you just need apple, banana and orange keys.

Collectives™ on Stack Overflow

Postgres Build Complex JSON Object from Wide Column Like Design to Key Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related