Proper use of field reference on JSON column in H2 database query?

H2 does not have JSON extraction functions like JSON_VALUE(my_json, '$.myFieldName') (e.g. MySQL). However, the H2 Grammar reference for "field reference" states:

( expression ) . fieldName
Returns field value from the row value or JSON value. Returns NULL if value is null or field with specified name isn't found in JSON. Expression on the left must be enclosed in parentheses if it is an identifier (column name), in other cases they aren't required.

(My emphasis)

I have a H2 database, version 2.2.220 (newest is 2.2.224 of 2023-09-17, at time of writing).
I am fairly OK with general SQL (Oracle, MySQL, PostgreSQL etc.), but quite new to H2 specifics.

My table looks something like this (simplified and names obfuscated):

create table my_table (
    id        int generated by default as identity,
    my_json   JSON null default null,
);

An example of data inserted into this table:

insert into my_table (my_json) values ('{ "myFieldName": "foo bar" }');

My query:

select id, my_json, (my_json).myFieldName as baz from my_table;

Results: I only get NULL values returned for the baz alias - all others are as expected. I do not get any error with this syntax. However, I also do not get the wanted result foo bar.

Can this be used as I am intending (to extract a field from a JSON object), and am I using this correctly? If not, how should I use it?

The question JSON_VALUE usage in H2 database grapples with the same sort of problem; an answerer has suggested either

a custom function, or
using regular expressions.

I do not want to go through the complexity to create custom functions and import 3rd party libraries since this database is used only for local development (project team's decision...), while a proper DB engine of another dialect is used when deployed, so not looking at (1).

I have been able to use some adaptation of the regular expression method together with REGEXP_SUBSTR(), but this leaves clumsy code (regular expressions are involved after all), and I am wondering about simpler alternatives. It does look suspicious to me that H2 provides functions to create JSON objects and arrays, but not use them also (apart from the above, as far as I can tell).

Solution

You need to use a JSON literal here:

insert into my_table (my_json) values (JSON '{ "myFieldName": "foo bar" }');

Attempt to insert a character string literal into JSON column in H2 creates a JSON string value.

You also need to quote names of JSON object fields:

select id, my_json, (my_json)."myFieldName" as baz from my_table;

Unquoted identifiers are converted by default to the upper case.

Proper use of field reference on JSON column in H2 database query?

Related