Tag: json

JSON_QUOTE() – How to Escape Characters in Strings used as JSON Values in MySQL

Posted on by Ian

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.

Continue reading →

JSON_OBJECT() – Create a JSON Object from a List of Key/Value Pairs in MySQL

Posted on by Ian

In MySQL, you can use the JSON_OBJECT() function to create a JSON object from a list of key/value pairs. You provide each key/value pair as two separate arguments. Each pair becomes a key/value pair in the resulting JSON object.

You must provide an even number of arguments (otherwise, you’d have an incomplete pair somewhere in your list of arguments).

The function also accepts an empty list (i.e. you provide no arguments). In this case, you’ll get an empty object.

Continue reading →

JSON_OBJECTAGG() – Create a JSON Object from Query Results in MySQL

Posted on by Ian

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.

Continue reading →

JSON_QUERY() vs JSON_VALUE() in SQL Server: What’s the Difference?

Posted on by Ian

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().

Continue reading →