Real-world example

[coveritytest]

Could you please give an example on how to convert this real world json to sqlite:

{
    "tradeHistory":{
       "orders":[
          {
             "id":"aaaa",
             "name":"a",
             "subOrders":[

             ],
             "cash":null
          },
          {
             "id":"bbbb",
             "name":"b",
             "subOrders":[

             ],
             "cash":null
          }
       ],
       "pageCount":2,
       "isSuperWikifolio":false
    },
    "tooltips":{
       "download-price":"<div>aaaa</div>",
       "download-account-statement":"<div>bbbb</div>"
    }
}

The table should hold the orders only. I suppose, I'd have to convert it to single line json with jq, but that already fails.

[dcmoura]

If you only need what is inside the orders array, the most compact way of getting that is using jq:

$ jq -c ".tradeHistory.orders[]" issue49.json
{"id":"aaaa","name":"a","subOrders":[],"cash":null}
{"id":"bbbb","name":"b","subOrders":[],"cash":null}

Here we are only getting the orders array and then we explode it so that we have 1 row per order. The -c option is required so that jq outputs one json per line, which is a requirement for spyql. We could have used spyql to do the exact same thing, but we still need jq -c to get one json per line:

$ jq -c . issue49.json | spyql "SELECT json->tradeHistory->orders AS json FROM json EXPLODE json->tradeHistory->orders TO json"
{"id": "aaaa", "name": "a", "subOrders": [], "cash": null}
{"id": "bbbb", "name": "b", "subOrders": [], "cash": null}

Either way, you could then use spyql to export to sql inserts:

$ jq -c ".tradeHistory.orders[]" issue49.json | spyql "SELECT json->id, json->name, json->cash FROM json TO sql"
INSERT INTO "table_name"("id","name","cash") VALUES ('aaaa','a',NULL),('bbbb','b',NULL);

Now, you just need to pipe it into sqlite:

$ jq -c ".tradeHistory.orders[]" issue49.json | spyql -Otable=my_table_name "SELECT json->id, json->name, json->cash FROM json TO sql" | sqlite3 my.db

You should have previously created the table my_table_name in the my.db database. We have plans to include the option to also generate the CREATE statement with spyql, but currently we only generate INSERTs.

[coveritytest]

That's great, thanks!