{"id":12015,"date":"2023-11-02T04:11:41","date_gmt":"2023-11-02T11:11:41","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12015"},"modified":"2023-11-02T08:13:01","modified_gmt":"2023-11-02T15:13:01","slug":"mysql-json_contains","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_contains\/","title":{"rendered":"MySQL JSON_CONTAINS() Function"},"content":{"rendered":"\n

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

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

The JSON_CONTAINS()<\/code> function checks whether a JSON document (target<\/code>) contains another JSON document (candidate<\/code>) at a path<\/a>.<\/p>\n\n\n\n

Here’s the syntax of the JSON_CONTAINS<\/code> function:<\/p>\n\n\n

JSON_CONTAINS(target, candidate[, 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
  • target<\/code>: This is the target JSON document in which you want to check for the presence of the candidate JSON document.<\/li>\n\n\n\n
  • candidate<\/code>: This is the candidate JSON document you want to check for within the target JSON document.<\/li>\n\n\n\n
  • path<\/code>:  This is the path within the target JSON document where you want to check for the candidate JSON document. The path argument is optional.<\/li>\n<\/ul>\n\n\n\n
    The JSON_CONTAINS()<\/code> function returns 1 if the target JSON document contains the candidate JSON document or 0 otherwise.<\/p>\n\n\n\n
    The JSON_CONTAINS()<\/code> function returns NULL<\/code> if any argument is NULL<\/code> or if the path argument does not identify a section of the target document.<\/p>\n\n\n\n
    The function issues an error in the following cases: <\/p>\n\n\n\n
      \n
    • The target or candidate is not a valid JSON document.<\/li>\n\n\n\n
    • The path<\/code><\/em> argument is not a valid path expression.<\/li>\n\n\n\n
    • The path contains a *<\/code> or **<\/code> wildcard.<\/li>\n<\/ul>\n\n\n\n
      MySQL JSON_CONTAINS() function examples<\/h2>\n\n\n\n
      Let’s explore some examples to understand how the JSON_CONTAINS()<\/code> function works.<\/p>\n\n\n\n
      1) Checking for Scalar Values<\/h3>\n\n\n\n
      The following example uses the JSON_CONTAINS()<\/code> function to check if the scalar value 1 is contained within a target JSON document at the path '$.a'<\/code>:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_CONTAINS(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    '1'<\/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<\/code> because the candidate the scalar 1<\/code> is contained within the target JSON document at the specified path ‘$.a<\/code>‘.<\/p>\n\n\n\n
      2) Checking for object elements<\/h3>\n\n\n\n
      The following example uses the JSON_CONTAINS()<\/code> function to check if a candidate JSON object {\"d\": 4}<\/code> is contained within the target JSON document at the path $.c<\/code>:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_CONTAINS(\n    '{\"a\": 1, \"b\": 2, \"c\": {\"d\": 4}}'<\/span>, \n    '{\"d\": 4}'<\/span>, '$.c'<\/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 function returns 1<\/code> because the candidate JSON object is contained within the target JSON document at the specified path $.c<\/code>.<\/p>\n\n\n\n
      3) Checking for array elements<\/h3>\n\n\n\n
      The following example uses the JSON_CONTAINS()<\/code> function to check if the target JSON document contains target JSON array at the path $.numbers<\/code>:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_CONTAINS(\n    '{\"numbers\": [1, 2, 3, 4, 5], \"fruits\": [\"apple\", \"banana\", \"cherry\"]}'<\/span>, \n    '[1, 2, 3]'<\/span>, '$.numbers'<\/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 function returns 1 <\/p>\n\n\n\n
      The result is 1<\/code> because the JSON array [1, 2, 3]<\/code> is contained within the target JSON document at the specified path $.numbers<\/code>.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the JSON_CONTAINS()<\/code> function to check if a JSON document contains another JSON document at a specified path.<\/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