{"id":18427,"date":"2021-07-10T10:01:39","date_gmt":"2021-07-10T00:01:39","guid":{"rendered":"https:\/\/database.guide\/?p=18427"},"modified":"2021-07-10T10:01:39","modified_gmt":"2021-07-10T00:01:39","slug":"mariadb-json_quote-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_quote-explained\/","title":{"rendered":"MariaDB JSON_QUOTE() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_QUOTE()<\/code> is a built-in function that produces a valid JSON string literal that can be included in a JSON document. <\/p>\n\n\n\n

It wraps the string with double quote characters and escapes interior quotes and other special characters, returning a utf8mb4 string.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

JSON_QUOTE(json_value)<\/code><\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SELECT JSON_QUOTE('Small') AS 'Result';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+\n| Result  |\n+---------+\n| \"Small\" |\n+---------+<\/pre>\n\n\n\n
Escape Characters<\/h2>\n\n\n\n
In addition to wrapping the string in double quotes, JSON_QUOTE()<\/code> also escapes interior quotes and other special characters.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT JSON_QUOTE('By \"small\", she meant \"tiny\"') AS 'Result';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------+\n| Result                             |\n+------------------------------------+\n| \"By \\\"small\\\", she meant \\\"tiny\\\"\" |\n+------------------------------------+<\/pre>\n\n\n\n
In this case, the original string contains double quotes and so the resulting string escapes each double quote with a backslash. <\/p>\n\n\n\n
If the double quotes weren’t escaped, they would interfere with the outer double quotes, and inadvertently terminate the string early. <\/p>\n\n\n\n
JSON Objects<\/h2>\n\n\n\n
Here’s an example of quoting a string representation of a JSON object:<\/p>\n\n\n\n
SELECT JSON_QUOTE('{ \"name\": \"Brandy\"}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------+\n| JSON_QUOTE('{ \"name\": \"Brandy\"}') |\n+-----------------------------------+\n| \"{ \\\"name\\\": \\\"Brandy\\\"}\"         |\n+-----------------------------------+<\/pre>\n\n\n\n
Arrays<\/h2>\n\n\n\n
Here’s an example of quoting a string representation of an array:<\/p>\n\n\n\n
SELECT JSON_QUOTE('[ \"Small\", \"Medium\", \"Large\" ]');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------------------------+\n| JSON_QUOTE('[ \"Small\", \"Medium\", \"Large\" ]') |\n+----------------------------------------------+\n| \"[ \\\"Small\\\", \\\"Medium\\\", \\\"Large\\\" ]\"       |\n+----------------------------------------------+<\/pre>\n\n\n\n
Numbers<\/h2>\n\n\n\n
Passing a number returns NULL<\/code>:<\/p>\n\n\n\n
SELECT JSON_QUOTE(10) AS 'Result';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n| NULL   |\n+--------+<\/pre>\n\n\n\n
The number would need to be provided as a string. It would then be returned as a quoted string.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT JSON_QUOTE('10') AS 'Result';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n| \"10\"   |\n+--------+<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If the argument is NULL<\/code>, the result is NULL<\/code>:<\/p>\n\n\n\n
SELECT JSON_QUOTE(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| JSON_QUOTE(null) |\n+------------------+\n| NULL             |\n+------------------+<\/pre>\n\n\n\n
Incorrect Parameter Count<\/h2>\n\n\n\n
Calling JSON_QUOTE()<\/code> without an argument results in an error:<\/p>\n\n\n\n
SELECT JSON_QUOTE();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUOTE'<\/pre>\n\n\n\n
It’s the same when you provide too many arguments:<\/p>\n\n\n\n
SELECT JSON_QUOTE('a', 'b');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUOTE'<\/pre>\n\n\n\n
More Examples<\/h2>\n\n\n\n
MariaDB JSON_QUOTE()<\/code> works the same as the MySQL function of the same name. See JSON_QUOTE()<\/code> – How to Escape Characters in Strings used as JSON Values in MySQL<\/a> for more examples.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_QUOTE() is a built-in function that produces a valid JSON string literal that can be included in a JSON document.  It wraps the string with double quote characters and escapes interior quotes and other special characters, returning a utf8mb4 string.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,80,20],"class_list":["post-18427","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18427"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18427\/revisions"}],"predecessor-version":[{"id":18538,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18427\/revisions\/18538"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}