MariaDB: inconsistent SELECT results involving JSON

Question

When executing the following statments on MariaDB 10.6.14

SET @json_1 = JSON_OBJECT('id', 'name');
SET @json_result_1 = JSON_OBJECT('test', @json_1);
SELECT @json_result_1;
SET @json_result_2 = JSON_OBJECT('test', JSON_OBJECT('id', 'name'));
SELECT @json_result_2;

I receive the following results:

For @json_result_1:

{"test": "{\"id\": \"name\"}"}

And for @json_result_2:

{"test": {"id": "name"}}

How does it come that I retrieve different result? Aren't those JSON string supposed to be internally all treated as TEXT?

Answer 1

So. After not even a day in the bug tracker for MariaDB I got the following back and the ticket was closed:

It behaves as expected. MariaDB knows that the output of JSON_OBJECT() is a json. But when you store it in a variable, it loses its "json-ness" and becomes a literal string.

To force a string to be interpreted as a json, you can use, indeed, JSON_EXTRACT, or, just JSON_COMPACT().

But also, this is my thought about it:

In my opinion it should keep the "json-ness" or whenever JSON_OBJECT uses that "literal string" it should check whether it's a valid json and treat it like that? That would make it way more intuitive.