{"id":12041,"date":"2023-11-02T17:33:57","date_gmt":"2023-11-03T00:33:57","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12041"},"modified":"2023-11-02T17:40:23","modified_gmt":"2023-11-03T00:40:23","slug":"mysql-json_set","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_set\/","title":{"rendered":"MySQL JSON_SET() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_SET()<\/code> function to replace existing values or add non-existing values to a JSON document.<\/p>\n\n\n\n

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

The JSON_SET()<\/code> function allows you to replace existing values or add non-existing values to a JSON document.<\/p>\n\n\n\n

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

JSON_REPLACE(json_doc, path, value[, path, val] ...)<\/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 replace or add data.<\/li>\n\n\n\n
  • path<\/code>: The\u00a0JSON path\u00a0that specifies the positions within the\u00a0json_doc<\/code>\u00a0where you want to replace or add data.<\/li>\n\n\n\n
  • value<\/code>: The value you want to replace or add.<\/li>\n<\/ul>\n\n\n\n
    The JSON_SET()<\/code> function returns the updated JSON document. It returns NULL<\/code> if any argument is NULL<\/code>.<\/p>\n\n\n\n
    When you use multiple path\/value pairs, the JSON_SET()<\/code> function will process them one by one starting from the left. It uses the updated JSON document of the first evaluation for the next one till the last one.<\/p>\n\n\n\n
    MySQL JSON_SET() function examples<\/h2>\n\n\n\n
    We’ll take some examples of using the JSON_SET()<\/code> function.<\/p>\n\n\n\n
    1) Replacing an existing value<\/h3>\n\n\n\n
    The following example uses the JSON_SET()<\/code> function to replace an existing value with a new value in a JSON document:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_SET(\n    '{\"name\": \"Jane Doe\", \"age\": 22}'<\/span>, \n    '$.name'<\/span>, \n    'Jane Smith'<\/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>\r\n| result                            |\r\n+-----------------------------------+<\/span>\r\n| {\"age\": 22, \"name\": \"Jane Smith\"} |\r\n+-----------------------------------+<\/span>\r\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 JSON_SET()<\/code> function to replace the name<\/code> in the object of the JSON document with a new one. <\/p>\n\n\n\n
    The path expression $.name<\/code> specifies the name<\/code> property of the object.<\/p>\n\n\n\n
    2) Replacing values in nested objects<\/h3>\n\n\n\n
    The following example uses the JSON_SET()<\/code> function to replace value within a nested object:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_SET(\n    '{\"person\": {\"name\": \"John\", \"address\": {\"city\": \"New York\"}}}'<\/span>, \n    '$.person.address.city'<\/span>, \n     'Los Angeles'<\/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>\r\n| result                                                           |\r\n+------------------------------------------------------------------+<\/span>\r\n| {\"person\": {\"name\": \"John\", \"address\": {\"city\": \"Los Angeles\"}}} |\r\n+------------------------------------------------------------------+<\/span>\r\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 JSON_SET()<\/code> function to update the city<\/code> within the nested object.<\/p>\n\n\n\n
    The path expression $.person.address.city<\/code> specifies the city<\/code> property of the address<\/code> object of the person<\/code> object.<\/p>\n\n\n\n
    3) Adding a nonexisting value<\/h3>\n\n\n\n
    If you set a value at a path that doesn’t exist in the JSON document, the JSON_SET()<\/code> function will add the element. For example:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_SET(\n    '{\"name\": \"John\", \"age\": 22}'<\/span>, '$.city'<\/span>, \n    'London'<\/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>\r\n| result                                        |\r\n+-----------------------------------------------+<\/span>\r\n| {\"age\": 22, \"city\": \"London\", \"name\": \"John\"} |\r\n+-----------------------------------------------+<\/span>\r\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) Replacing\/adding multiple path\/value pairs<\/h3>\n\n\n\n
    The following example uses the SET()<\/code> function to both update an existing value and add a new value to a JSON document:<\/p>\n\n\n
    SELECT<\/span> \n  JSON_SET(\n    '{\"name\": \"Jane Doe\", \"age\": 25, \"city\": \"New York\"}'<\/span>, \n    '$.name'<\/span>, 'Jane Smith'<\/span>, \n    '$.job'<\/span>, 'MySQL Specialist'<\/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>\r\n| result                                                                           |\r\n+----------------------------------------------------------------------------------+<\/span>\r\n| {\"age\": 25, \"job\": \"MySQL Specialist\", \"city\": \"New York\", \"name\": \"Jane Smith\"} |\r\n+----------------------------------------------------------------------------------+<\/span>\r\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:<\/p>\n\n\n\n
      \n
    • The path $.name<\/code> locates the name property that exists in the JSON document, therefore, the function replaces the current name \"Jane Doe\"<\/code> with the new one \"Jane Smith\"<\/code>.<\/li>\n\n\n\n
    • The path $.job<\/code> locates the job<\/code> property that doesn’t exist in the JSON document so the function inserts the new property job<\/code> with the value \"MySQL Specialist\"<\/code>.<\/li>\n<\/ul>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the MySQL JSON_REPLACE()<\/code> function to replace existing values in 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