我有以下数据集:
{"date":{"year":2017,"month":7,"day":2},"data":{"param1":[10,20,30,440],"param2":[55,65,75,85],"param3":[43,55,71,72]}}
我从一个名为rawData的表中检索数据。
现在我想取消对数组中的值的测试,并将它们放在另一个表(separateData)中的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
我想我必须用最不必要的和平凡的方式来实现这一点,但我仍然坚持我应该如何继续下去。数组param1、param2、param3的长度始终相同。
我已经对数据做了手脚:
https://www.db-fiddle.com/f/jZhYLEvdSERzLCDyoAJz65/0
最佳答案
当然不是最好的方法,我的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