将mongoDb-json文件转换为SQL表

Question

我有一个mongo DB Json文件，我想以某种方式将其压缩到SQL数据库中，并使其有意义，但我不知道它会是什么样子。文件如下：

"_id":{
"$oid":"61377659d24fd78398a5a54a"
},
"currentTotalValue":0,
"stocks":[
{
"ticker":"TSLA",
"baseCurrency":"USD",
"numberOfShares":20
},
{
"ticker":"GME",
"baseCurrency":"USD",
"numberOfShares":100
},
{
"ticker":"KINV-B",
"baseCurrency":"SEK",
"numberOfShares":50
},
{
"ticker":"BBD.B",
"baseCurrency":"CAD",
"numberOfShares":100
},
{
"ticker":"NAS",
"baseCurrency":"NOK",
"numberOfShares":20000
}
]
}
{
"_id":{
"$oid":"61377666d24fd78398a5a558"
},
"currentTotalValue":0,
"stocks":[
{
"ticker":"TSLA",
"baseCurrency":"USD",
"numberOfShares":1
},
{
"ticker":"GME",
"baseCurrency":"USD",
"numberOfShares":3457
},
{
"ticker":"KINV-B",
"baseCurrency":"SEK",
"numberOfShares":3547
},
{
"ticker":"BBD.B",
"baseCurrency":"CAD",
"numberOfShares":5768
},
{
"ticker":"NAS",
"baseCurrency":"NOK",
"numberOfShares":100000
}
]
}

那么，在SQL中，这是两个单独的表，每个表中有一个投资组合吗？如果是这样的话，这些桌子会是什么样子？

Answer 1

这取决于如何解释文件。可以说，有两个投资组合由一些oid和一个currentTotalValue描述，这两个股票属于

CREATE TABLE portfolios (
oid VARCHAR(24) NOT NULL PRIMARY KEY,
currentTotalValue INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE stocks (
id INTEGER NOT NULL PRIMARY KEY, 
portfolio_id VARCHAR(24) NOT NULL,
ticker VARCHAR(20) NOT NULL,
baseCurrency VARCHAR(3) NOT NULL,
numberOfShares INTEGER NOT NULL,
CONSTRAINT fk_stocks_portfolios 
FOREIGN KEY(portfolio_id) REFERENCES portfolios(oid)
);

如果你不需要投资组合，你可以放弃它，并从表股票中删除外键约束：

CREATE TABLE stocks (
id INTEGER NOT NULL PRIMARY KEY, 
portfolio_id VARCHAR(24) NOT NULL,
ticker VARCHAR(20) NOT NULL,
baseCurrency VARCHAR(3) NOT NULL,
numberOfShares INTEGER NOT NULL
);

警告：这只是一个粗略的草图，以获得一个基本的想法。根据使用的DBMS，您可以为库存id使用自动增值。