{"id":18451,"date":"2021-07-12T09:55:20","date_gmt":"2021-07-11T23:55:20","guid":{"rendered":"https:\/\/database.guide\/?p=18451"},"modified":"2021-07-12T09:55:20","modified_gmt":"2021-07-11T23:55:20","slug":"mariadb-json_search-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_search-explained\/","title":{"rendered":"MariaDB JSON_SEARCH() Explained"},"content":{"rendered":"\n<p>In MariaDB, <code>JSON_SEARCH()<\/code> is a built-in function that allows you to get the path to a given value in a JSON document.<\/p>\n\n\n\n<p>It accepts the JSON document and a string as arguments, and returns the path to the given string within the document.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JSON_SEARCH(\n    json_doc, \n    return_arg, \n    search_str&#91;, escape_char&#91;, path] ...]\n    )<\/code><\/pre>\n\n\n\n<p>Where: <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>json_doc<\/code> is the JSON document and <code>search_str<\/code> is the string.<\/li><li><code>return_arg<\/code> is the keyword <code>one<\/code> or <code>all<\/code>. If you use <code>one<\/code>, only the first path is returned. Any other occurrences are ignored. Which path is considered &#8220;first&#8221; is undefined (according to the <a rel=\"noreferrer noopener\" href=\"https:\/\/mariadb.com\/kb\/en\/json_search\/\" target=\"_blank\">MariaDB documentation<\/a>). If <code>all<\/code> is specified, the paths of all occurrences are returned. If there are multiple paths, they&#8217;re auto-wrapped as an array.<\/li><li>The <code>escape_char<\/code> argument is an optional character to use as an escape character.<\/li><li>The <code>path<\/code> argument is an optional argument to determine where the &#8220;top-level&#8221; path starts within the JSON document.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s an example to demonstrate:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '\n    { \n        \"name\" : \"Wag\", \n        \"type\" : \"Dog\" \n    }';\n\nSELECT JSON_SEARCH(@json, 'one', 'Wag');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+----------------------------------+\n| JSON_SEARCH(@json, 'one', 'Wag') |\n+----------------------------------+\n| \"$.name\"                         |\n+----------------------------------+<\/pre>\n\n\n\n<p>Here&#8217;s an example of returning the path for an element in an array:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '\n    { \n        \"product\" : \"Left Handed Screwdriver\", \n        \"sizes\" : &#91; \"Small\", \"Medium\", \"Large\" ],\n    }';\n\nSELECT JSON_SEARCH(@json, 'one', 'Medium');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-------------------------------------+\n| JSON_SEARCH(@json, 'one', \"Medium\") |\n+-------------------------------------+\n| \"$.sizes[1]\"                        |\n+-------------------------------------+<\/pre>\n\n\n\n<p>Arrays are zero-based, and so <code>$.sizes[1]<\/code> refers to the second element in the array.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Multiple Occurrences<\/h2>\n\n\n\n<p>If you want to return all paths that contain the string, use <code>all<\/code> instead of <code>one<\/code> for the second argument.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '&#91;\n    { \"name\": \"Wag\", \"type\": \"Dog\", \"weight\": 20 },\n    { \"name\": \"Bark\", \"type\": \"Dog\", \"weight\": 10 },\n    { \"name\": \"Meow\", \"type\": \"Cat\", \"weight\": 7 }\n]';\n\nSELECT JSON_SEARCH(@json, 'all', 'Dog');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+----------------------------------+\n| JSON_SEARCH(@json, 'all', \"Dog\") |\n+----------------------------------+\n| [\"$[0].type\", \"$[1].type\"]       |\n+----------------------------------+<\/pre>\n\n\n\n<p>If we change <code>all<\/code> to <code>one<\/code>, here&#8217;s what happens: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '&#91;\n    { \"name\": \"Wag\", \"type\": \"Dog\", \"weight\": 20 },\n    { \"name\": \"Bark\", \"type\": \"Dog\", \"weight\": 10 },\n    { \"name\": \"Meow\", \"type\": \"Cat\", \"weight\": 7 }\n]';\n\nSELECT JSON_SEARCH(@json, 'one', 'Dog');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+----------------------------------+\n| JSON_SEARCH(@json, 'one', \"Dog\") |\n+----------------------------------+\n| \"$[0].type\"                      |\n+----------------------------------+<\/pre>\n\n\n\n<p>Only one path is returned. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Specify a Path<\/h2>\n\n\n\n<p>Here&#8217;s an example that specifies a path for which to search within the document:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\", \n            \"weight\" : 20,\n            \"awards\" : { \n                \"NZ Dog Award\" : \"Top Dog\", \n                \"New York Marathon\" : \"Fastest Animal\", \n                \"Sumo 2021\" : \"Biggest Dog\"\n            }\n        }\n    }\n';\nSELECT JSON_SEARCH(\n    @json, \n    'all',\n    '%dog%',\n    NULL,\n    '$.details.awards'\n    ) AS Result;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------------------------------------------------------+\n| Result                                                          |\n+-----------------------------------------------------------------+\n| [\"$.details.awards.NZ Dog Award\", \"$.details.awards.Sumo 2021\"] |\n+-----------------------------------------------------------------+<\/pre>\n\n\n\n<p>In this case, the string <code>dog<\/code> actually occurs three times within the document, but only twice below the specified path.<\/p>\n\n\n\n<p>Also, we used <code>NULL<\/code> for the escape character argument, which results in the default escape character being used, which is the backslash (<code>\\<\/code>). <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Default Escape Character<\/h2>\n\n\n\n<p>By default, the escape character is a backslash (<code>\\<\/code>). <\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '&#91;\n    { \"uid\": \"Wag\", \"pwd\": \"my%pwd\" },\n    { \"uid\": \"Bark\", \"pwd\": \"my%%%pwd\" },\n    { \"uid\": \"Bark\", \"pwd\": \"myBIGpwd\" }\n]';\n\nSELECT \n    JSON_SEARCH(@json, 'all', 'my%pwd') AS \"Not Escaped\",\n    JSON_SEARCH(@json, 'all', 'my\\%pwd') AS \"Escaped\";<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+--------------------------------------+------------+\n| Not Escaped                          | Escaped    |\n+--------------------------------------+------------+\n| [\"$[0].pwd\", \"$[1].pwd\", \"$[2].pwd\"] | \"$[0].pwd\" |\n+--------------------------------------+------------+<\/pre>\n\n\n\n<p>The percentage sign (<code>%<\/code>) is a wildcard character that matches any number of characters. Therefore, if we don&#8217;t escape it, then it will match any number of characters, including characters that are not percentage signs.<\/p>\n\n\n\n<p>But when we escape the percentage sign with the escape character, it will only match when there is exactly one percentage sign in that location. <\/p>\n\n\n\n<p>The above results reflect this.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Specify a Custom Escape Character<\/h2>\n\n\n\n<p>You can specify a custom escape character if required. To do this, provide it as the fourth argument.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json = '&#91;\n    { \"uid\": \"Wag\", \"pwd\": \"my%pwd\" },\n    { \"uid\": \"Bark\", \"pwd\": \"my%%%pwd\" },\n    { \"uid\": \"Bark\", \"pwd\": \"myBIGpwd\" }\n]';\n\nSELECT \n    JSON_SEARCH(@json, 'all', 'my%pwd', '!') AS \"Not Escaped\",\n    JSON_SEARCH(@json, 'all', 'my!%pwd', '!') AS \"Escaped\";<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+--------------------------------------+------------+\n| Not Escaped                          | Escaped    |\n+--------------------------------------+------------+\n| [\"$[0].pwd\", \"$[1].pwd\", \"$[2].pwd\"] | \"$[0].pwd\" |\n+--------------------------------------+------------+<\/pre>\n\n\n\n<p>So we get the same result as in the previous example. The only difference is that we specified a different escape character. In this case, we specified that the exclamation mark (<code>!<\/code>) is the escape character.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Null Arguments<\/h2>\n\n\n\n<p>If any of the search string, search string, or path arguments are <code>NULL<\/code>, the result is <code>NULL<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    JSON_SEARCH(null, 'all', 's', '', '$') AS a,\n    JSON_SEARCH('{\"a\":1}', 'all', null, '', '$') AS b,\n    JSON_SEARCH('{\"a\":1}', 'all', 's', '', null) AS c;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+------+------+------+\n| a    | b    | c    |\n+------+------+------+\n| NULL | NULL | NULL |\n+------+------+------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Incorrect Parameter Count<\/h2>\n\n\n\n<p>Providing no arguments results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_SEARCH();<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_SEARCH'<\/pre>\n\n\n\n<p>It&#8217;s the same when you provide too few arguments:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_SEARCH('{\"a\":1}', 'all');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_SEARCH'<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In MariaDB, JSON_SEARCH() is a built-in function that allows you to get the path to a given value in a JSON document. It accepts the JSON document and a string as arguments, and returns the path to the given string within the document.<\/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-18451","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\/18451","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=18451"}],"version-history":[{"count":14,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18451\/revisions"}],"predecessor-version":[{"id":18467,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18451\/revisions\/18467"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}