How does one convert a JSON string value to a VARCHAR value (excluding quotes) in H2?

Question

I have a H2 database (version 2.2.220) with data in a JSON column; I need to migrate the column via a SQL script so that the data is in a conventional relational database structure. The main stumbling block is that on extraction of a JSONValue string value (surrounded by double quotes in JSON format), I am struggling to convert this into a VARCHAR value that does not include the surrounding double quotes.

I hope the below SQL statements illustrate the issue in the most rudimentary way:

1. create table my_table (
       id        int generated by default as identity,
       my_json   JSON null default null,
   );
   
   insert into my_table (my_json) values (JSON '"abcdef"');
   

   The double quotes inside the string constant is necessary, else it is not recognized as valid JSON.
2. 1st query (naive):

   select my_json from my_table;
   

   This returns "abcdef" with quotes included in the string. I would however like the raw string value abcdef to be returned without the surrounding quotes.
3. 2nd query (typecasting):

   select CAST(my_json as VARCHAR(255)) from my_table;
   

   This still returns "abcdef" with quotes included in the string.
4. 3rd query ("brute-force" trimming):

   select BTRIM(my_json, '"') from my_table;
   

   This achieves the desired result abcdef, but is problematic. One way this will cause a bug is when the string value should intentionally include a double quote at the beginning and/or end, e.g. xyz" (JSON '"xyz\""') - this will be trimmed to return xyz\.

I am wondering if there is a "recommended"/cleaner way to do this conversion that is less bug-prone.

Related/Interesting

• Wikipedia: JSON data types
• Stackoverflow: What is the minimum valid JSON?
• JSON formatter & validator