json - 如何将 JSON 提取到表中

Question

我有一个充满 json 文档的数据库，每行一个，如下所示:

[{
    "ID": "TOT",
    "type": "ABS",
    "value": "32.0"
},
{
    "ID": "T1",
    "type": "ABS",
    "value": "9.0"
},
{
    "ID": "T2",
    "type": "ABS",
    "value": "8.0"
},
{
    "ID": "T3",
    "type": "ABS",
    "value": "15.0"
}]

我需要提取这些信息以便将数据插入到这样的表中:

     pod          T1    T2    T3    TOT
IT001E18486545    9.0   8.0  15.0   32.0

我试过以下方法:

select pod, json_array_elements(jsond::json) ->>'value' as value, 
json_array_elements(jsond::json) ->>'ID' as ID,  
json_array_elements(jsond::json) ->>'type' as stype
from tst_json

但是这样我得到了这样的结果

这不是我需要的。

Answer 1

您可以使用 case ... then ... end 构造来旋转数据:

select 
    pod, 
    case when value->>'ID' = 'T1' then value->>'value' end as "T1",
    case when value->>'ID' = 'T2' then value->>'value' end as "T2",
    case when value->>'ID' = 'T3' then value->>'value' end as "T3",
    case when value->>'ID' = 'TOT' then value->>'value' end as "TOT"
from tst_json
cross join json_array_elements(jsond)

      pod       | T1  | T2  |  T3  | TOT  
----------------+-----+-----+------+------
 IT001E18486545 |     |     |      | 32.0
 IT001E18486545 | 9.0 |     |      | 
 IT001E18486545 |     | 8.0 |      | 
 IT001E18486545 |     |     | 15.0 | 
(4 rows)

使用 max() 和 group by 为每个 pod 获取一行并去除空值:

select 
    pod, 
    max(case when value->>'ID' = 'T1' then value->>'value' end) as "T1",
    max(case when value->>'ID' = 'T2' then value->>'value' end) as "T2",
    max(case when value->>'ID' = 'T3' then value->>'value' end) as "T3",
    max(case when value->>'ID' = 'TOT' then value->>'value' end) as "TOT"
from tst_json
cross join json_array_elements(jsond)
group by pod

      pod       | T1  | T2  |  T3  | TOT  
----------------+-----+-----+------+------
 IT001E18486545 | 9.0 | 8.0 | 15.0 | 32.0
(1 row)