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?

Answer 1

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
                }
            ]
        }
    ]
}