MySQL – JSON Functions Adding Double Quotes Through Variable

jsonmariadbmariadb-10.2MySQL

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

Best Answer

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

JSON-validated text.
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.

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

MySQL – How to Insert Escaped Double Quotes into JSON Field

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.

I'll talk to the MySQL docs team about adding some examples and explanation for that here:

12.6 The JSON Data Type - Creating JSON Values

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

MySQL – How to Insert Escaped Double Quotes into JSON Field

Related Question