所以我整天都在try 处理这些JSON数据和Postgr,但不知道我做了什么导致了这个问题.
这就是我的数据集的样子,整个数据集大约有1000个对象:
{
avgHighPrice: null,
highPriceVolume: 0,
avgLowPrice: 1633,
lowPriceVolume: 1,
id: '267'
},
{
avgHighPrice: 3581,
highPriceVolume: 1,
avgLowPrice: 3501,
lowPriceVolume: 290,
id: '269'
},
{
avgHighPrice: 800,
highPriceVolume: 1,
avgLowPrice: null,
lowPriceVolume: 0,
id: '272'
}, ...
我正在try 将id,avgHighPrice,avgLowPrice添加到下面的Postgr表中
CREATE TABLE ticker_summary (
id INT PRIMARY KEY references items,
open_high INT,
open_low INT,
high_price INT,
low_price INT,
high_vol INT,
low_vol INT,
last_update INT
);
我在Node中使用以下代码执行此操作
await db.query("INSERT INTO ticker_summary (id, open_high, open_low) SELECT x.id, x.avgHighPrice, x.avgLowPrice FROM json_to_recordset($1) AS x (avgHighPrice INT, highPriceVolume INT, avgLowPrice INT, lowPriceVolume INT, id INT);", [JSON.stringify(array)]);
其中array
是对象的array.
当我这样做的时候,代码成功执行,但它只插入了id值,其他所有内容都是空的,我假设这意味着它不匹配字段与定义的名称,但我不知道为什么.
有什么 idea 吗?
Postgr测试调试代码
INSERT INTO ticker_summary (id, open_high, open_low)
SELECT x.id,
x.avgHighPrice,
x.avgLowPrice
FROM json_to_recordset('[{"avgHighPrice":153,"highPriceVolume":61308,"avgLowPrice":151,"lowPriceVolume":11252,"id":"2"},{"avgHighPrice":194920,"highPriceVolume":1,"avgLowPrice":182001,"lowPriceVolume":1,"id":"6"},{"avgHighPrice":187121,"highPriceVolume":4,"avgLowPrice":178642,"lowPriceVolume":2,"id":"10"}]')
AS x (avgHighPrice INT, highPriceVolume INT, avgLowPrice INT, lowPriceVolume INT, id INT);
我需要从数组中的每个对象中提取3个值,但到Postgr中自己的行中.