{"id":12010,"date":"2023-11-02T03:37:02","date_gmt":"2023-11-02T10:37:02","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12010"},"modified":"2023-11-02T08:13:06","modified_gmt":"2023-11-02T15:13:06","slug":"mysql-json_contains_path","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_contains_path\/","title":{"rendered":"MySQL JSON_CONTAINS_PATH() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_CONTAINS_PATH()<\/code> function to check whether a JSON document contains specific paths.<\/p>\n\n\n\n

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

The JSON_CONTAINS_PATH<\/code> function is used to check whether a JSON document contains specific paths<\/a>.<\/p>\n\n\n\n

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

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)<\/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_doc<\/code>: This is the JSON document you want to search for the paths.<\/li>\n\n\n\n
  • one_or_all<\/code>: A keyword that specifies whether you want to find at least one or all of the specified paths. If it is 'one'<\/code>, the function returns 1 if the JSON document contains at least one, 0 otherwise. If the parameter is 'all'<\/code>, the function returns 1 if the document contains all the specified paths and 0 otherwise.<\/li>\n\n\n\n
  • path<\/code>: This is one or more path expressions you want to check within the JSON document.<\/li>\n<\/ul>\n\n\n\n
    The function is useful when you want to check if certain paths exist within the JSON document.<\/p>\n\n\n\n
    MySQL JSON_CONTAINS_PATH() function examples<\/h2>\n\n\n\n
    Let’s take some examples of using the JSON_CONTAINS_PATH<\/code> function.<\/p>\n\n\n\n
    1) Checking for the Existence of a Single Path<\/h3>\n\n\n\n
    The following example uses the JSON_CONTAINS_PATH()<\/code> function to check if the JSON document contains the path $.a<\/code>: <\/p>\n\n\n
    SELECT<\/span> \n  JSON_CONTAINS_PATH(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    'one'<\/span>, '$.a'<\/span>\n  ) result<\/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| result |\n+--------+<\/span>\n|      1 |\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
    The result is 1 because the JSON document contains the path '$.a'<\/code>.<\/p>\n\n\n\n
    2) Checking for Multiple Paths with ‘one’ Mode<\/h3>\n\n\n\n
    The following example uses the JSON_CONTAINS_PATH<\/code> function to check if the JSON document contains at least one of two specified paths, $.a<\/code> and $.e<\/code>:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_CONTAINS_PATH(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    'one'<\/span>, '$.a'<\/span>, '$.e'<\/span>\n  ) result<\/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| result |\n+--------+<\/span>\n|      1 |\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
    The result is 1<\/code> because the JSON document contains at least one of the specified paths ($.a<\/code>).<\/p>\n\n\n\n
    3) Checking for Multiple Paths with ‘all’ Mode<\/h3>\n\n\n\n
    The following example uses the JSON_CONTAINS_PATH()<\/code> function to check if the JSON document contains all of the specified paths, $.a<\/code> and $.e<\/code>:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_CONTAINS_PATH(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    'all'<\/span>, '$.a'<\/span>, '$.e'<\/span>\n  ) result<\/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| result |\n+--------+<\/span>\n|      0 |\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, the function returns 0 because not all of the specified paths exist in the JSON document.<\/p>\n\n\n\n
    4) Nested paths<\/h3>\n\n\n\n
    The following example uses the JSON_CONTAINS_PATH()<\/code> function to check if the JSON document contains the nested path $.c.d<\/code>:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_CONTAINS_PATH(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    'one'<\/span>, '$.c.d'<\/span>\n  ) result<\/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| result |\n+--------+<\/span>\n|      1 |\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, the result is 1<\/code> because the path $.c.d<\/code> exists in the JSON document.<\/p>\n\n\n\n
    5) Non-Existent Path example<\/h3>\n\n\n\n
    The following example uses the JSON_CONTAINS_PATH()<\/code> function to check if the JSON document contains the path $.a.d<\/code>.<\/p>\n\n\n
    SELECT<\/span> \n  JSON_CONTAINS_PATH(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    'one'<\/span>, '$.a.d'<\/span>\n  ) result<\/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| result |\n+--------+<\/span>\n|      0 |\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
    The function returns 0<\/code> because the JSON document does not contain the path $.a.d<\/code>.<\/p>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the JSON_CONTAINS_PATH()<\/code> function to check whether a JSON document contains specific paths.<\/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