MySQL includes an aggregate function called JSON_OBJECTAGG(). This function enables you to create a JSON object containing key-value pairs. More specifically, it lets you create this JSON object based on the results of a query.
It accepts two arguments, the first of these being used as a key and the second as a value. These arguments can be column names or expressions.
Among the many MySQL aggregate functions is one called JSON_ARRAYAGG(). This function enables you to aggregate a result set as a single JSON array. Each row of the result set ends up as a single element in the array.
The order of the elements in the array is undefined.
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.
Two of the many T-SQL functions available in SQL Server are JSON_QUERY() and JSON_VALUE(). These functions can be used to extract data from JSON documents.
Their general syntax is similar, and at first glance, you might think they do exactly the same thing, but they don’t. There’s definitely a place for both functions when working with JSON and SQL Server.
This article looks at the difference between JSON_QUERY() and JSON_VALUE().
When using JSON with SQL Server, you can use the JSON_QUERY() function to extract an object or an array from a JSON string.
To use this function, you provide the JSON expression as an argument. You can also provide a second (optional) argument to specify the object or array to extract.
When using JSON with SQL Server, you can use the JSON_VALUE() function to return a scalar value from a JSON string.
To use this function, you provide two arguments; the JSON expression, and the property to extract.
When using SQL Server, you can use the ISJSON() function to test whether or not a string expression contains valid JSON.
If the expression contains valid JSON, ISJSON() returns 1, otherwise it returns 0.
From SQL Server 2022, we also have the option of testing for a specific JSON type.
Continue readingWhen using SQL Server, you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the PATH option.
In SQL Server you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the AUTO option.