json functions: double quotes added when going through a variable Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go through variables, it works fine. Unfortunately I have to go through variables as I'm building my JSON using a recursive function:


without variables:

declare temp_json JSON;
set temp_json = json_object('data',json_object("jsonapi",json_object("version", "1.0")));
select temp_json


OUTPUT

{"data": {"jsonapi": {"version": "1.0"}}}

with variables

declare temp_json JSON;
declare jsonapiheader JSON;
set jsonapiheader = json_object("jsonapi",json_object("version", "1.0"));
set temp_json = json_object('data',jsonapiheader);
select temp_json


OUTPUT

{"data": "{\"jsonapi\": {\"version\": \"1.0\"}}"}` 



So how do I do like 2) but with the output of 1) ?

my system:
MariaDB 10.2.9
@Evan Carroll is certainly right about the general implementation of JSON in MariaDB.
But if you are still going with MariaDB this might help you:

The behavior you describe is a serious bug which is tracked here:
https://jira.mariadb.org/browse/MDEV-13701
As far as I understood its expected to be fixed with MariaDB-10.2.12.

For the time being I use the following hack:

set @jsonapiheader = json_object("jsonapi",json_object("version", CAST(1.0 AS DECIMAL(2,1))));
set @temp_json = concat('{"data":',@jsonapiheader,'}');
select @temp_json, json_valid(@temp_json);


Output:

{"data":{"jsonapi": {"version": 1.0}}}        |       1


Also look at the CAST within the first JSON_OBJECT. It also works with variables and functions and results in a more accurate JSON output.