Postgres从宽列设计到关键值构建复杂的JSON对象

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:

我想构建一个如下所示的JSON对象：

{
  "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.

我知道有json_each可以做到这一点。但我正在努力将其应用于查询。

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:

编辑：这是我更新的查询，我猜，它非常接近。我放弃了用json_each解决它的想法。现在我只需要返回一个水果数组，而不是附加到fruits对象：

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?

我是否需要添加子查询以防止嵌套聚合函数？

1 个解决方案

#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:

使用函数jsonb_each（）来获取对（键，值），因此您无需知道列数及其名称即可获得正确的输出：

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

#1