时间戳

首页 > 解决方案 > 在 Postgres 中创建 JSON

sql - 在 Postgres 中创建 JSON

问题描述

在我们的数据库中，我们有几个组成略有不同的表。我们想在 postgres 中构建 1 个函数，该函数能够从它中生成具有某种特定结构的 JSON。

以下是两个示例表：

CREATE TEMPORARY TABLE tmp_experience( level 
character varying , value numeric , score 
numeric, mean numeric );

INSERT INTO tmp_experience( level , value , 
score , mean ) VALUES ( 'high' , 0.23 , 30 , 
0.45 ) , ('low' , 0.63 , 45 , 0.56 );

CREATE TEMPORARY TABLE tmp_gender( level 
character varying , value numeric , 
percentage numeric );

INSERT INTO tmp_gender( level , value , 
percentage ) VALUES ( 'male' , 2.23 , 35 ) , 
('female' , 1.63 , 65  );

在我们想要制作的两个 JSON 下方：

{
  "high": {
    "value": 0.23,
    "score": 30,
    "mean": 0.45
  },
  "low": {
    "value": 0.63,
    "score": 45,
    "mean": 0.56
  }
}

{
  "male": {
    "value": 2.23,
    "score": 35
  },
  "female": {
    "value": 1.63,
    "score": 65
  }
}

我们希望 Postgres 中有一个函数可以做到这一点。我认为这相对简单（也许确实如此！），但我根本不知道该怎么做。

标签： sqljsonpostgresql

解决方案

使用聚合函数jsonb_object_agg()：

select jsonb_object_agg(level, to_jsonb(t)- 'level')
from tmp_experience t;

                                            jsonb_object_agg                                             
---------------------------------------------------------------------------------------------------------
 {"low": {"mean": 0.56, "score": 45, "value": 0.63}, "high": {"mean": 0.45, "score": 30, "value": 0.23}}
(1 row)

select jsonb_object_agg(level, to_jsonb(t)- 'level')
from tmp_gender t

                                     jsonb_object_agg                                     
------------------------------------------------------------------------------------------
 {"male": {"value": 2.23, "percentage": 35}, "female": {"value": 1.63, "percentage": 65}}
(1 row)

更新

您需要与预期级别一样多的 SELECT。您可以将派生表（FROM 子句中的子查询）用于嵌套聚合：

-- jsonb_pretty() not necessary, used for readable output
select jsonb_pretty(jsonb_object_agg(country, levels)) as countries
from (
    select country, jsonb_object_agg(level, to_jsonb(t)- 'country'- 'level') as levels
    from tmp_experience t
    group by country
    ) s

或公用表表达式：

with levels as (
    select country, jsonb_object_agg(level, to_jsonb(t)- 'country'- 'level') as levels
    from tmp_experience t
    group by country
)
select jsonb_pretty(jsonb_object_agg(country, levels)) as countries
from levels;

rextester 中的工作示例。

推荐阅读