In SQLite, the json() function converts raw text that looks like JSON into actual JSON.

We pass a JSON string as an argument when we call the function. The json() function then checks that the argument is a valid JSON string and returns a minified version of that JSON string. If the argument is not a well-formed JSON string, then an error is thrown.

However, the json() function was not designed to test whether or not a value is valid JSON. To do that, use the json_valid() function instead.

Syntax

json(X)

Where X is the value to be checked and minified.

Example

SELECT json('{ "a": 1 }');

Result:

{"a":1}

In this case, I provided a valid JSON document, and so a minified version of it was returned (unnecessary spaces were removed).

Here’s an example that uses a larger JSON document with more spaces:

SELECT json('{
	"_id": 1.0,
	"title": "Animals",
	"body": "blah blah 1",
	"tags": [
		"cats",
		"dogs"
	]
}');

Result:

{"_id":1.0,"title":"Animals","body":"blah blah 1","tags":["cats","dogs"]}

The json() function can be useful for when you need to pass JSON to another function. As mentioned it converts raw text that looks like JSON into actual JSON, which makes it ideal for when you need to pass it to the other function. The other function will then interpret the value as JSON rather than a string.

Invalid JSON

Passing an invalid JSON string results in an error:

SELECT json('{oops!');

Result:

Runtime error: malformed JSON

Duplicate Labels

If a JSON document contains duplicate labels, there’s no guarantee that they will always be preserved.

At the time of writing, duplicate labels are preserved. However, the SQLite documentation advises that this could change in a future version of SQLite, so that duplicate labels are silently removed.

Here’s an example that uses a JSON document with duplicate labels:

SELECT json('{ "a": 1, "b": 1, "a" : 2 }');

Result:

{"a":1,"b":1,"a":2}

In this case, my JSON document has two labels called a. Both were preserved in my SQLite installation (version 3.38.0), but this may not always be the case in future SQLite versions.