Transforming mongoDb json file into SQL tables-CodePudding

I have a mongo DB Json file that I in some way want to squeeze into a SQL database and have it make sense but I don't know what that would look like. The file looks like this:

"_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
      }
   ]
}

So in SQL is this two separate tables with one portfolio in each or? And if so what would those tables look like?

CodePudding user response：

It depends on how one interprets the file. One could say, there are two portfolios which are described by some oid and a currentTotalValue two which the stocks belong to.

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)
);

If you don't need the portfolio you can drop it and remove the foreign key constraint from table stocks:

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
);

Warning: this is just a rough sketch to get a basic idea. Depending on the used DBMS you could use an auto increment value for the stocks id.