In SQLite, the json_quote() function converts a number or string into its corresponding JSON representation.

We provide the number or string as an argument when we call the function, and the function returns a JSON representation of that value.

Syntax

The syntax goes like this:

json_quote(X)

Where X is a number or string.

Examples

Here’s what happens when we pass a string to the function:

SELECT json_quote('Super');

Result:

"Super"

And here’s a number:

SELECT json_quote(10.45);

Result:

10.45

Here’s a string that looks like an array:

SELECT json_quote('[10.45]');

Result:

"[10.45]"

Here’s a string that looks like an object:

SELECT json_quote('{ "score" : 10.45 }');

Result:

"{ \"score\" : 10.45 }"

But if we pass the argument in the json() function, we get this:

SELECT json_quote(json('{ "score" : 10.45 }'));

Result:

{"score":10.45}

Null Values

Passing null returns null:

SELECT json_quote( null );

Result:

null

This is actually the SQL text value null. We can verify this by passing it to the json_type() function:

SELECT json_type(json_quote( null ));

Result:

null

The json_type() function returns the SQL text value type of its argument. In this case it returned null, which indicates that the json_quote() function returned null when we passed null to it.

In case we suspect that our SQLite CLI is returning null due to an actual null value being returned, we can do the following to rule that out:

.nullvalue N/A

That tells our command line interface to return N/A whenever a null value is returned.

Having run the above command, let’s run the previous statements again, as well as an operation that actually results in a null value:

SELECT 
    json_quote( null ) AS json_quote,
    json_type(json_quote( null )) AS json_type,
    1 / 0 AS actual_null;

Result:

+------------+-----------+-------------+
| json_quote | json_type | actual_null |
+------------+-----------+-------------+
| null       | null      | N/A         |
+------------+-----------+-------------+

In SQLite, dividing a number by zero results in a null value (many other DBMSs throw an error in such cases). In this example, I had set null values to return N/A, and so we can see that there’s a difference between the output of the two functions and the actual null value. That is, the only null value in the above output is in the last column.

Having said all that, a null value can still result in the SQL text value null being returned when calling json_quote(). Perhaps an example illustrates this better than I can explain it:

SELECT 
    json_quote( 1 / 0 ),
    json_type(json_quote( 1 / 0 )),
    json_type( 1 / 0 ),
    1 / 0;

Result:

+---------------------+--------------------------------+--------------------+-------+
| json_quote( 1 / 0 ) | json_type(json_quote( 1 / 0 )) | json_type( 1 / 0 ) | 1 / 0 |
+---------------------+--------------------------------+--------------------+-------+
| null                | null                           | N/A                | N/A   |
+---------------------+--------------------------------+--------------------+-------+

We can see that the first two columns returns the null SQL text value, and the other two columns return an actual null value.

Passing Booleans

Here’s an example of passing booleans such as true and false:

SELECT 
    json_quote( true ) AS true,
    json_quote( false ) AS false;

Result:

+------+-------+
| true | false |
+------+-------+
| 1    | 0     |
+------+-------+

Invalid Argument Count

Calling the function without passing an argument results in an error:

SELECT json_quote();

Result:

Parse error: wrong number of arguments to function json_quote()
  SELECT json_quote();
         ^--- error here

And passing too many arguments also results in an error:

SELECT json_quote( 1, 2 );

Result:

Parse error: wrong number of arguments to function json_quote()
  SELECT json_quote( 1, 2 );
         ^--- error here