MySQL 5.7 - Query to set the value of a JSON key to a JSON Object

Question

Using MySQL 5.7, how to set the value of a JSON key in a JSON column to a JSON object rather than a string.

I used this query:

SELECT json_set(profile, '$.twitter', '{"key1":"val1", "key2":"val2"}')
from account WHERE id=2

Output:

{"twitter": "{\"key1\":\"val1\", \"key2\":\"val2\"}", "facebook": "value", "googleplus": "google_val"}

But it seems like it considers it as a string since the output escapes the JSON characters in it. Is it possible to do that without using JSON_OBJECT()?

Elias Van Ootegem · Accepted Answer · 2019-01-16 12:31:48Z

1

There's a couple of options that I know of:

Use the JSON_UNQUOTE function to unquote the output (ie not cast it to string) as documented here
Possibly use the ->> operator and select a specific path, documented here
Has a lot of implications, but you could disable backslashes as an escape character. I haven't tried this, so I don't even know if that works, but it's mentioned in the docs

On balance, I'd either use the ->> operator, or handle the conversion on the client side, depending on what you want to do.

answered Jan 16, 2019 at 12:31

Elias Van Ootegem

76.7k10 gold badges123 silver badges160 bronze badges

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

Collectives™ on Stack Overflow

MySQL 5.7 - Query to set the value of a JSON key to a JSON Object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related