In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result.
You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert. You can provide multiple path/value pairs if you need to update multiple values.
When working with JSON and MySQL, sometimes you might need to escape strings so that their quote characters don’t interfere with the interpretation of the JSON document. In such cases, you can use the JSON_QUOTE() function to escape potentially problematic strings.
JSON_QUOTE() is used to produce a valid JSON string literal that can be included in a JSON document. For example, you might want an element in an array to have the text null as a string literal, rather than having an actual null value. This function can be used to ensure that the text is added as a string literal, rather than a null value.
To use it, simply call the function while passing in the string.
The MySQL MIN() function is an aggregate function that returns the minimum value from an expression.
Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the minimum value from the returned rows. If there are no matching rows, MIN() returns NULL.
For example, you can use this function to find out which city has the smallest population out of a list of cities.
The MySQL MAX() function is an aggregate function that returns the maximum value from an expression.
Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the maximum value from the returned rows. If there are no matching rows, MAX() returns NULL.
For example, you can use this function to find out which city has the largest population out of a list of cities.
If you’ve been using the JSON_MODIFY() function to modify JSON documents in SQL Server, you might be used to modifying the value part of a key/value property. But did you know that you can also modify the key part?
The trick to doing this is to copy the value to a new key, then delete the old key.
Examples below.
In SQL Server, you can use the T-SQLÂ JSON_MODIFY() function to modify the value of a property in a JSON string. The function returns the updated JSON string.
There are certain date functions in MySQL that the return a day name or month name. In particular, I’m referring to the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. These might return a value of say, November, or Monday, depending on the query being used. But the results could just as easily be returned in a different language if required.
The language that these functions use for their return value is derived from the lc_time_names system variable. You can view the value of this variable or set its SESSION value so that the results of these functions is in the desired language/locale.
Locale names have language and region subtags listed by the Internet Assigned Numbers Authority (IANA). Examples include en_US for English – United States, en_NZ for English – New Zealand, or es_PA for Spanish – Panama, etc (for a list of locales supported by MySQL, see Full List of Locales in MySQL).
In this article, I’ll show you how to find the current locale for your connection, change it, and then see how it affects the results of a query. I also show you a function that’s immune to this setting (but don’t worry, this function allows you to specify the locale).
In SQL Server, the first day of the week is determined by the current language settings. You can also override that with the SET DATEFIRST statement, which allows you to explicitly set the first day of the week.
In either case, you can use the @@DATEFIRST function to find out what settings your session is using for the first day of the week. This article demonstrates how.
In SQL Server, you can use SET DATEFIRST to set the first day of the week.
The first day of the week can be different, depending on the language being used. For example the default for us_English is 7 (Sunday), whereas the default for Deutsch (German) is 1 (Monday).
This article demonstrates how to change the first day of the week without changing the language.
In SQL Server, you can use the YEAR() function to return the “year” part of a date. This is returned as an integer.
Below are examples of how to use this function.