MYSQL: JSON_OBJECT with binary data

Question

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)) ));

When I copy your query, it seems like the length of your string is 25 characters rather than 16. What server version/connection character set are you using? — danblack
– danblack, Commented Sep 15, 2018 at 4:19
I am using MYSQL 5.7 Also, when I copy from this query, it won't work. — R O
– R O, Commented Sep 15, 2018 at 4:23
I don't see a BIN_TO_UUID function in MySQL-5.7 and I get the error 'Error: ER_SP_DOES_NOT_EXIST: FUNCTION test.BIN_TO_UUID does not exist' in 5.7. Is it a plugin/or function you have create/copied/installed? — danblack
– danblack, Commented Sep 15, 2018 at 4:27
I edited the question and add function I am currently using. — R O
– R O, Commented Sep 15, 2018 at 4:32
That helps, change BIN_TO_UUID to return a VARCHAR. I'm not sure your function is right. Seems to be returning a different result than MySQL-8.0 — danblack
– danblack, Commented Sep 15, 2018 at 4:40

danblack · Accepted Answer · 2018-09-15 05:07:08Z

3

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,

answered Sep 15, 2018 at 5:07

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3413723 · Accepted Answer · 2022-09-11 01:59:46Z

0

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–’Ú¦[¦£´Æ')));

answered Sep 11, 2022 at 1:59

user3413723

12.5k6 gold badges62 silver badges72 bronze badges

Collectives™ on Stack Overflow

MYSQL: JSON_OBJECT with binary data

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related