Will there be a virtual table/ table valued function which would allow the input of one big json text block and get a table of repeated rows from it?
I got inspired to ask this when getting in contact with Python using the pandas module and pickles by the Python nodes in [KNIME Analytics Platform](https://www.knime.com/). A pickle is some kind of blob of a serialized data stream. Isn't a database or just a table or view out of it something similar?
It would be great to become enabled to use json text as a kind of pickle to carry configuration and parameter information and on one path a whole table content. Such a json text could carry more than only a table in a human readable and streamable format. I suggest to enable the CLI to accept JSON as an import format.
I would appreciate a table valued function (similar to json_each/json_tree that is able to create a whole data table output while the json text itself could also carry additional info, even the whole schema. JSON could even become an import type at the CLI. Let me show you my idea by an example:
~~~
sqlite> .mode box
sqlite> select * from concerts;
┌─────┬──────────────┬─────────────┐
│ id │ artist │ concertDate │
├─────┼──────────────┼─────────────┤
│ 100 │ FOO FIGHTERS │ 2019-03-22 │
│ 101 │ JOE COCKER │ 2007-03-22 │
│ 102 │ ELO │ 1998-04-18 │
│ 103 │ YES │ 2003-11-02 │
└─────┴──────────────┴─────────────┘
sqlite> .schema
CREATE TABLE concerts (
id integer not null,
artist text not null,
concertDate text not null
);
sqlite> .mode json
sqlite> select * from concerts;
[{"id":100,"artist":"FOO FIGHTERS","concertDate":"2019-03-22"},
{"id":101,"artist":"JOE COCKER","concertDate":"2007-03-22"},
{"id":102,"artist":"ELO","concertDate":"1998-04-18"},
{"id":103,"artist":"YES","concertDate":"2003-11-02"}]
~~~
The accepted input could then e.g. be from following JSON-Schema definition, especially the JSON path "$.DATAPACKET[1].DATA":
~~~
{ "DATAPACKET" : [ SCHEMA-VERSION":"3.38.0"
, { "TABLENAME":"concerts"
, "FIELDS":[ { "NAME":"id" , "DATATYPE:"integer", CONSTRAINT:["not null","primary key"] }
, { "NAME":"artist" , "DATATYPE":text" , CONSTRAINT:["not null"] }
, { "NAME":"concertDate" , "DATATYPE":text" , CONSTRAINT:["not null"] }
]
, "DATA":[ {"id":100, "artist":"FOO FIGHTERS","concertDate":"2019-03-22"}
, {"id":101, "artist":"JOE COCKER", "concertDate":"2007-03-22"}
, {"id":102, "artist":"ELO", "concertDate":"1998-04-18"}
, {"id":103, "artist":"YES", "concertDate":"2003-11-02"}
]
}
]
}
~~~