在 postgresql (10.3) 中取消嵌套并在单独的行和列中插入 json 数据

Question

我有以下数据集：

{"date":{"year":2017,"month":7,"day":2},"data":{"param1":[10,20,30,440],"param2":[55,65,75,85],"param3":[43,55,71,72]}}

我从一个名为 rawData 的表中检索数据。

现在我想在数组中取消嵌套值，并将它们放在另一个表 (seperateData( 中的 4 个单独的行和列中，因此对于此示例，它看起来像：

year  | month | day | param1 | param2 | param3
2017      7      2      10       55       43
2017      7      2      20       65       55
2017      7      2      30       75       71
2017      7      2      440      85       72

我猜我必须使用unnest和秩序来实现这一目标，但被困在我应该如何进行。数组 param1、param2、param3 始终具有相同的长度。

我已经对数据进行了摆弄： https://www.db-fiddle.com/f/jZhYLEvdSERzLCDyoAJz65/0

Answer 1

当然不是最好的方法，我的PostgreSQL经验不是那么好，但它有效。

查询

SELECT   
((rawData.values)::json->'date')::json->'year' AS year
, ((rawData.values)::json->'date')::json->'month' AS month
, ((rawData.values)::json->'date')::json->'day' AS day  
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param1') AS param1
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param2') AS param2   
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param3') AS param3
FROM
rawData

演示 https://www.db-fiddle.com/f/jZhYLEvdSERzLCDyoAJz65/1

当数组 param1、param2、param3 的长度不相等时，查询也有效。

演示 https://www.db-fiddle.com/f/jZhYLEvdSERzLCDyoAJz65/4

感谢您的回答，到目前为止它有效。我唯一忘记的是，我 实际上也需要索引作为单独的列。所以索引的 数组元素。任何想法

这可以通过GENERATE_SERIES与JSON_ARRAY_LENGTH结合使用来实现

查询

SELECT   
((rawData.values)::json->'date')::json->'year' AS year
, ((rawData.values)::json->'date')::json->'month' AS month
, ((rawData.values)::json->'date')::json->'day' AS day  
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param1') AS param1
, GENERATE_SERIES(1, JSON_ARRAY_LENGTH(((rawData.values)::json->'data')::json->'param1')) AS param1_array_index
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param2') AS param2   
, GENERATE_SERIES(1, JSON_ARRAY_LENGTH(((rawData.values)::json->'data')::json->'param2')) AS param2_array_index    
, JSON_ARRAY_ELEMENTS(((rawData.values)::json->'data')::json->'param3') AS param3
, GENERATE_SERIES(1, JSON_ARRAY_LENGTH(((rawData.values)::json->'data')::json->'param3')) AS param3_array_index        
FROM
rawData

查看演示 https://www.db-fiddle.com/f/jZhYLEvdSERzLCDyoAJz65/6