MYSQL: JSON_OBJECT with binary data

user_9914620 0
Viewed 1226

When I select JSON_OBJECT from database it will give me the unexpected result.

select JSON_OBJECT('test', BIN_TO_UUID('GÇ~pw–’Ú¦[¦£´Æ'));

{ "test": "base64:type254:Nzc5NjkyZGEtN2U3MC00N2M3LWE2MWMtNWJhNmEzYjQxZWM2"}

I want the result like this {"test": "779692da-7e70-47c7-a61c-5ba6a3b41ec6"}

I am using MYSQL 5.7.

Also, when I copy from this query, it won't work. This code will work properly. SELECT JSON_OBJECT('test', BIN_TO_UUID(UUID_TO_BIN(UUID())));

This is my function for UUID_TO_BIN and BIN_TO_UUID

CREATE FUNCTION UUID_TO_BIN(_uuid BINARY(36))
RETURNS BINARY(16)
LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER
RETURN
UNHEX(CONCAT(
    SUBSTR(_uuid, 15, 4),
    SUBSTR(_uuid, 10, 4),
    SUBSTR(_uuid,  1, 8),
    SUBSTR(_uuid, 20, 4),
    SUBSTR(_uuid, 25) ));

CREATE FUNCTION BIN_TO_UUID(_bin BINARY(16))
RETURNS BINARY(36)
LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER
RETURN
LCASE(CONCAT_WS('-',
    HEX(SUBSTR(_bin,  5, 4)),
    HEX(SUBSTR(_bin,  3, 2)),
    HEX(SUBSTR(_bin,  1, 2)),
    HEX(SUBSTR(_bin,  9, 2)),
    HEX(SUBSTR(_bin, 11)) ));
mysql
2 Answers

JSON_OBJECT inspects the return type when deciding how it will encode the data. BINARY data types get encoded as base64 as the question shows.

Changing the return type on BIN_TO_UUID to VARCHAR will make the encoding use a pure text format when encoding this to JSON,

user_101951530

I had this issue when using JSON_ARRAYAGG on binary data. I have a custom UUID I store as binary sometimes. It's kind of like the firebase UUID - binary data, but it works like ascii. Anyways, since it came up in search, this is what worked for me.

JSON_OBJECT(key, CAST(column_name as char));

So I'm guessing you could do

select JSON_OBJECT('test', CAST(BIN_TO_UUID('GÇ~pw–’Ú¦[¦£´Æ')));
user_34137230
Related