1

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:

  1. 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"}}}
    
  2. 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

1

First, generally speaking, there are two types of JSON,

  1. JSON-validated text.
  2. Binary JSON (Mongo calls it BSON, PostgreSQL calls it JSONB, also Apache Avro).

For reference MariaDB has neither of these. It has a JSON type, but that type is nothing more than un-validated text, from the docs

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

If you need a validated column, you need to use a CHECK constraint. So essentially, internally all the functions that "do JSON", essentially read text process it internally and output text.

So the problem you're running into is that json_object doesn't know that the value is JSON-text. There isn't an easy around that. The only option from a MariaDB's perspective is to implement each function as accepting a value, and another one as accepting a jdoc, or doing it right and implementing a real type for JSON.

As a side note, there is one function that does accept two jdocs, one to operate on, and one as an argument. That function is JSON_MERGE.

See also,

I would highly suggest migrating to PostgreSQL if it's not too late. Not just do you get a binary JSON type but you get a rich library of functions, and operators, and indexes for that type.

  • I had to google and it appears that MariaDB now supports CHECK constraints. Not sure what they mean when they say that JSON contradicts the SQL standard? – Lennart Jan 3 '18 at 22:15
  • @Lennart no idea, but I did look it up (also curious there) dba.stackexchange.com/q/194681/2639 be interested in their reading too. – Evan Carroll Jan 5 '18 at 0:26
2

@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.