If you’re trying to extract values from a JSON document, but one or more of the values on the same level have duplicate keys, you might run into problems if you try to extract those values using JSON_QUERY() or JSON_VALUE().
Both of those functions will only return the first value that matches the path.
Fortunately, there’s another option.
The OPENJSON() function will return all values from any duplicate keys on the same level.
If you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with'”, one possibility is that you really do have a syntax error.
But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.
Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.
SQL Server has a table-valued function called OPENJSON() that creates a relational view of JSON data.
When you call it, you pass a JSON document as an argument, and OPENJSON() then parses it and returns the JSON document’s objects and properties in a tabular format – as rows and columns.
If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.
OPENJSON() is only available under compatibility level 130 or higher.
To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.
Continue readingIn MySQL, the JSON_LENGTH() function returns the length of a JSON document.
When you call this function, you provide the JSON document as an argument. You can also provide a path argument to return the length of a value within the document.
In MySQL, the JSON_DEPTH() function returns the maximum depth of a JSON document.
When you call this function, you provide the JSON document as an argument.
In MySQL, the JSON_TYPE() function returns the type of a JSON value. More specifically, it returns a utf8mb4 string indicating the value’s type.
You provide the JSON value as an argument.
In MySQL, the JSON_STORAGE_FREE() function shows how much storage space was freed up after a JSON Document was updated.
For a JSON column value, it shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE().
For a JSON document (or a string which can be parsed as one), this function returns 0.
In MySQL, the JSON_STORAGE_SIZE() function returns the number of bytes used to store the binary representation of a JSON document.
You provide the JSON document as an argument.
When the argument is a JSON column, this function returns the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.
When the argument is a string, the function returns the amount of storage space in the JSON binary representation that is created by parsing the string as JSON and converting it to binary.
In MySQL, the JSON_PRETTY() function provides pretty-printing of JSON values. It returns the JSON values in a nicely formatted way, which makes it easier for us humans to read.
You provide the JSON values as an argument to the function. The function then returns those values formatted according to its formatting rules.