MySQL includes some inbuilt functions that can be used to create JSON values, such as JSON arrays, JSON objects, or a quoted string literal.
Here are seven functions that allow us to create JSON values in MySQL.
Continue readingAuthor: Ian
How to Order a Query in SQL Server when using a Sequence to Number the Results
One common use case for sequences in SQL Server is to use them to number the results of a query. For example, we can add a column that has a number that increments with each row, similar to a row counter (but with a sequence that we define).
Continue readingMySQL JSON Functions & Operators
Below is a list of the JSON functions and operators available in MySQL.
Continue readingFix Error Msg 220 “Arithmetic overflow error for data type…” in SQL Server
If you’re getting error msg 220 that reads something like Arithmetic overflow error for data type…, it’s probably because you’re trying to convert a value to a data type that can’t handle that value. For example, trying to convert a number to a smallint but the number’s too big to fit into a smallint.
To fix this issue, make sure you convert the value to a data type that can handle the size of the number that you’re trying to convert.
Continue readingHow to Add a Root Element when using FOR XML in SQL Server
If you’re using FOR XML in SQL Server to output your query results as XML, you might find yourself in the situation where the XML returned has no root element. Fortunately adding a root element is easy, as I’ll show you in this article.
3 Ways to Unquote a JSON Value in MySQL
In MySQL, we have several options when it comes to removing the quotes from a JSON value.
For example, normally if we return a string value from a JSON document, we would get that value along with its quotes. But sometimes we might not want the quotes. Below are three methods we can use to unquote a JSON value in MySQL.
Continue readingFix “MINVALUE (…) must be less than MAXVALUE (…)” When Creating a Sequence in PostgreSQL
If you’re getting an error that reads something like “MINVALUE (1) must be less than MAXVALUE (1)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s minimum possible value is higher than the maximum value.
To fix this issue, be sure that the sequence’s maximum value is greater than the minimum value.
Continue reading2 Ways to Apply a Sequence to a Table in SQL Server
In SQL Server, we can create sequence objects in order to generate a range of numbers that increment with each call to the sequence.
Although sequences are similar to identity columns, they are different in that they are generated independently of any table. It’s up to the application to generate the next sequence number before inserting it into the table. Therefore, we can apply a sequence to a table in the same way we would insert any other data into a table.
Here are two ways to apply sequence numbers into a table in SQL Server.
Continue readingFix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZLEXCOUNT in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZLEXCOUNT command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that you pass a sorted set to the ZLEXCOUNT command.
Understanding the JSON_SCHEMA_VALIDATION_REPORT() Function in MySQL
In MySQL, the JSON_SCHEMA_VALIDATION_REPORT() function validates a JSON document against a JSON schema and returns a report of the outcome of that validation. The report is returned as a JSON document.
If the document is valid, only one field is returned: "valid" : true. If the document is not valid, a more extensive report is provided.