{"id":12117,"date":"2023-11-06T06:27:40","date_gmt":"2023-11-06T13:27:40","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12117"},"modified":"2023-11-06T06:28:42","modified_gmt":"2023-11-06T13:28:42","slug":"mysql-json_search","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_search\/","title":{"rendered":"MySQL JSON_SEARCH() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_SEARCH()<\/code> function to find a path for a given string within a JSON document.<\/p>\n\n\n\n

Introduction to MySQL JSON_SEARCH() function<\/h2>\n\n\n\n

The JSON_SEARCH()<\/code> function is used to find a path<\/a> for a given string within a JSON document.<\/p>\n\n\n\n

Here’s the syntax of the JSON_SEARCH()<\/code> function:<\/p>\n\n\n

JSON_SEARCH(json_document, one_or_all, search_str [, escape_char])<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  • json_document<\/code>: This is the JSON document in which you want to search.<\/li>\n\n\n\n
  • one_or_all<\/code>: The argument that specifies whether to find the first occurrence or all occurrences. If one_or_all<\/code> is ‘one’, the function stops the search after the first match and returns one path string. If the one_or_all<\/code> argument is ‘all’, then the function returns an array that contains all the matching paths.<\/li>\n\n\n\n
  • search_str<\/code>: This is the string or pattern you want to search for within the JSON document. With the search_str<\/code> argument, you can use the % and _ wildcard characters. The % matches any number of characters and _ matches exactly one character.<\/li>\n\n\n\n
  • escape_char<\/code>: To use the wildcard characters (% and _ ) as the literal string within the search_str<\/code>, you preceded it with an escape character (escape_char<\/code>). The default escape_char<\/code> is \\ if you omit it or if it is NULL<\/code>. Otherwise, escape_char<\/code> must be a constant that is empty or one character.<\/li>\n\n\n\n
  • path: Determines how to interpret the search string, ‘one’ as a path, ‘all’ as a wildcard pattern.<\/li>\n<\/ul>\n\n\n\n
    The JSON_SEARCH()<\/code> function returns NULL<\/code> if any of the json_doc<\/code>, search_str<\/code>, or path_mode<\/code> argument is NULL<\/code>.<\/p>\n\n\n\n
    The function issues an error in case one of the following conditions:<\/p>\n\n\n\n
      \n
    • The json_doc<\/code> argument is not a valid JSON document.<\/li>\n\n\n\n
    • Any path<\/code> argument is not a valid path expression.<\/li>\n\n\n\n
    • The value of the one_or_all<\/code> argument is not ‘one’ or ‘all’.<\/li>\n\n\n\n
    • escape_char<\/code> is not a constant expression.<\/li>\n<\/ul>\n\n\n\n
      MySQL JSON_SEARCH() function examples<\/h2>\n\n\n\n
      Let’s take some examples of using the JSON_SEARCH()<\/code> function.<\/p>\n\n\n\n
      1) Searching for the first occurrence<\/h3>\n\n\n\n
      The following example uses the JSON_SEARCH()<\/code> function to search for the first occurrence of a path that matches the string \"John\"<\/code> in a JSON document:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"name\": \"John\", \"age\": 25, \"username\": \"John\"}'<\/span>, \n    'one'<\/span>, \n    'John'<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      It returns the path expression “$.name”: <\/p>\n\n\n
      +----------+<\/span>\n| path     |\n+----------+<\/span>\n| \"$.name\" |\n+----------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      2) Searching for all occurrences<\/h3>\n\n\n\n
      The following example uses the JSON_SEARCH()<\/code> function to search for all occurrences of a path that matches the string \"John\"<\/code> in a JSON document:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"name\": \"John\", \"age\": 25, \"username\": \"John\"}'<\/span>, \n    'all'<\/span>, \n    'John'<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +--------------------------+<\/span>\n| path                     |\n+--------------------------+<\/span>\n| [\"$.name\", \"$.username\"] |\n+--------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      3) Searching with the wildcard %<\/h3>\n\n\n\n
      The following example uses the JSON_SEARCH()<\/code> function to search for all occurrences of a path that matches the string \"john\"<\/code> and is followed by any number of characters in a JSON document:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"name\": \"john\", \"age\": 25, \"email\": \"john@test.com\"}'<\/span>, \n    'all'<\/span>, \n    'john%'<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +-----------------------+<\/span>\n| path                  |\n+-----------------------+<\/span>\n| [\"$.name\", \"$.email\"] |\n+-----------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      4) Searching with the wildcard _<\/h3>\n\n\n\n
      The following example uses the JSON_SEARCH()<\/code> function to search for all occurrences of a path that matches any single character followed by the string \"oe\"<\/code> in a JSON document:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '[{\"name\": \"Joe\", \"age\": 25, \"salary\":\"100_000\"},\n      {\"name\": \"Doe\", \"age\": 27, \"salary\":\"120_000\"}]'<\/span>, \n    'all'<\/span>, \n    '_oe'<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +----------------------------+<\/span>\n| path                       |\n+----------------------------+<\/span>\n| [\"$[0].name\", \"$[1].name\"] |\n+----------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      5) Searching using an escape character<\/h3>\n\n\n\n
      Suppose we have the following JSON document:<\/p>\n\n\n
      {\"code\": \"S_100\", \"name\":\"Sx100\"}<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      To search for the path that matches the string \"S_100\"<\/code>, you may come up with the following statement:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"code\": \"S_100\", \"name\":\"Sx100\"}'<\/span>, \n    \"all\"<\/span>, \"S_100\"<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      But the search string \"S_100\"<\/code> matches both strings \"S_100\"<\/code> and \"Sx100\"<\/code> because the _<\/code> character in the search string S_100<\/code> is a wildcard character that matches both literal character _<\/code> in \"S_100\"<\/code> and x<\/code> in \"Sx100\"<\/code>:<\/p>\n\n\n
      +----------------------+<\/span>\n| path                 |\n+----------------------+<\/span>\n| [\"$.code\", \"$.name\"] |\n+----------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      To treat the character _<\/code> as a literal string in the search string \"S_100\"<\/code>, you need to escape it by preceding it with the default escape character (\\<\/code>) like this:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"code\": \"S_100\", \"name\":\"Sx100\"}'<\/span>, \n    \"all\"<\/span>, \"S\\_100\"<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +----------+<\/span>\n| path     |\n+----------+<\/span>\n| \"$.code\" |\n+----------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      It works as expected now.<\/p>\n\n\n\n
      If you don’t want to use the default escape character, you can specify one using the escape_char<\/code> argument. For example:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_SEARCH(\n    '{\"code\": \"S_100\", \"name\":\"Sx100\"}'<\/span>, \n    \"all\"<\/span>, \"S$_100\"<\/span>, '$'<\/span>\n  ) path<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +----------+<\/span>\n| path     |\n+----------+<\/span>\n| \"$.code\" |\n+----------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      In this example, we use the dollar ($<\/code>) as the escape character and precede the wildcard character (_<\/code>) with the escape character.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the JSON_SEARCH()<\/code> function to find a path for a given string within a JSON document.<\/li>\n<\/ul>\n
        \n\t
        \n\t\t
        Was this tutorial helpful? <\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t