JSONREAD. Can it be supported similarly to CSVREAD?

[George Ivanov]

Hi H2 Dev team. What do you think?

[Evgenij Ryazanov]

Hello.

If you simply want to read a JSON from a file, use

CAST(FILE_READ('/path/to/file.json') AS VARBINARY) FORMAT JSON

or something like it. You can't convert a BLOB to JSON directly, but you can use VARBINARY in the middle.

If you want to inspect structure of JSON, you need to use some third-party library instead; H2 has only JSON constructor functions and JSON predicate from SQL:2016. JSON query functions, like JSON_TABLE from SQL:2016 are unlikely to be implemented in the near future, because they require SQL/JSON path language support, this language is not as complicated as SQL, but still isn't trivial.

[George Ivanov]

Hi Evgenij,

I'd like JSON to be converted to the DB table or tables, same way as it is done to CSVREAD

[Evgenij Ryazanov]

You need JSON_TABLE for it, but it isn't implemented yet and AFAIK nobody works on its implementation.

[George Ivanov]

Thank you!

Can I place a feature request or something? Can I support this function implementation by a donation?

[George Ivanov]

Or, may be you see some workaround, such as a third party lib (as you mentioned). Which one can be used?

[Evgenij Ryazanov]

You can parse JSON with GSON, FasterXML/jackson, javax.json, or some other library.

[Matt Pavlovich]

George-

What is the structure of the JSON data? JSON data can have lots of different shapes to it, so its complicated to make an automatic JSON-to-database table import tool that works for all use cases. If the data is flat, like CSV, than you may look for an intermediate tool to convert it to CSV and then import to H2 using the existing CSV import tools.

A tool you may find useful is the open source AtlasMap-- (http://atlasmap.io) which I believe just added CSV support. You can use it to design and convert JSON to CSV. Disclaimer-- I created AtlasMap, but am not currently actively contributing and do not receive any financial benefit at this time.

If your JSON is not flat and you don't want to use existing tools, you'd need to write some intermediary tools to convert JSON data that is not flat to groups of flat data that look more like database tables-- as Evgenij is mentioning using Jackson, Johnson, or other JSON java library to read in the JSON data and convert out to a database friendly table structure.

-Matt Pavlovich

[George Ivanov]

Matt, Evgenij, thank you for your suggestions.

I've just tried the Jackson library (it can convert some JSON flat arrays to CSV which H2 can consume and convert to table) and it worked fine as a start.

However, yes, as a target I was looking for something that could convert not a flat (arbitrary) JSON data to relational view (a set of tables, etc. in H2 in-memory DB) 

[George Ivanov]

Hi Matt, Evgenij,

there is still a question about converting JSON to CSV and then using CSVREAD.

When the conversion is performed and CSV is read to the DB, the data looses its datatype (since CSV and CSVREAD apparently don't support datatypes other than VARCHAR in the DB).

So, I guess, JSONREAD (as least for a flat JSON arrays) would be useful, since it could be implemented in a way that would preserve the datatypes from original JSON.

What do you think? 

[Guillaume de GENTILE]

Hi,

<< CSVREAD apparently don't support datatypes other than VARCHAR in the DB >>

when reading data with CSVREAD, you are free to convert or CAST the strings to NUMERIC or DATE columns.

Example:
select
    CAST(COL1 as DOUBLE) as COL1,
    ...
from CSVREAD()


Regards

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/bf0f5e06-ba4c-4bbc-956c-be69aa722735o%40googlegroups.com.

[George Ivanov]

yes, however, I'd like the types to be automatically preserved from the original JSON types.

I don't want to manually populate the types. Instead I'd like, for example, to analyze the datatypes from JSON but in the H2 DB using SQL

[Matt Pavlovich]

"auto" is going to be subjective because JSON types aren't 1:1 to SQL types for number precision. To retain precision in JSON, one is forced to store those values as strings instead of numbers.

ref: https://www.w3schools.com/js/js_numbers.asp

[George Ivanov]

I have a related question:

let's say I have an in-memory table with 2 columns (A and B) and 1 row:

| A | B |

| 1 | 2 |

I am able to get the result of SQL execution over H2 in-memory DB as JSON resultset ( i.e. from stmt.getResultSet(), using gson ) as follows:

{

  "metaData": [

    "A",

    "B"

  ],

  "resultSet": [

    [

      "1",

      "2"

    ]

  ],

  "locator": {

    "headerFromNum": {

      "0": "A",

      "1": "B"

    },

    "headerFromName": {

      "a": 0,

      "b": 1

    },

    "throwExceptions": true,

    "NOT_IN_HEADER": -3467651

  }

}

However, I'd like to get the result as a flat array like this:

[

  {"A":"1", "B":"2"}

]

is there a simple way, say, to convert the H2 resultset to such an array?

[George Ivanov]

yah, that's true... thanks Matt!