我正试图从这样的表创建嵌套的json:

+----------+---------+------------------------------+
| unixtime | assetid |             data             |
+----------+---------+------------------------------+
|       10 |      80 | {"inflow": 10, "outflow": 2} |
|       20 |      90 | {"inflow": 10, "outflow": 2} |
|       10 |      80 | {"inflow": 10, "outflow": 2} |
|       20 |      90 | {"inflow": 10, "outflow": 2} |
+----------+---------+------------------------------+

得到这样的东西:
{
    "10": {
        "80": {"inflow": 10, "outflow": 2},
        "90": {"inflow": 10, "outflow": 2}
    },
    "20": {
        "80": {"inflow": 10, "outflow": 2},
        "90": {"inflow": 10, "outflow": 2}
    }
}

我尝试过递归地将json数据转换为文本,array_agg然后使用json_对象将结果转换为json blob,但这最终用转义斜杠(\)破坏了json结构。
任何帮助都将不胜感激
以下是数据链接:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=26734e87d4b9aea4ceded4e414acec4c
谢谢您。

最佳答案

您可以使用json_object_agg()函数:

....
, m as (
select
    unixdatetime,
    assetid,
    json_object(array_agg(description), array_agg(value::text))
    as value
from input_data
group by unixdatetime, assetid
), j as
(
select json_object_agg("assetid","value") as js,m."unixdatetime"
  from m
 group by "unixdatetime"
)
select json_object_agg("unixdatetime",js)
  from j

关于sql - 在PostgreSQL中创建嵌套的JSON Blob,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/57525995/

10-09 20:18
查看更多