SQLite json_pretty() Function

Summary: in this tutorial, you will learn how to use the SQLite json_pretty() function to make JSON human-readable.

Introduction to the SQLite json_pretty() function

The json_pretty() function works like the json() function but makes the JSON value prettier. This means that the json_pretty() function adds extra whitespace to make the JSON value more human-readable.

Note that SQLite added the json_pretty() function since version 3.46.0 on May 23, 2024.

Here’s the syntax of the json_pretty() function:

json_pretty(json_value, format)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_value is a valid JSON value to format.
• format is an optional argument used for indentation. If you omit the second argument or use NULL, the function will use four spaces to indent each level.

SQLite json_pretty() function examples

The following example uses the json_pretty() function to format a JSON value:

SELECT json_pretty('{"name": "Alice"     ,"age" : 25 , "address": {"street": "101 N 1st Street", "city": "San Jose"} }');Code language: SQL (Structured Query Language) (sql)

Output:

{
    "name": "Alice",
    "age": 25,
    "address": {
        "street": "101 N 1st Street",
        "city": "San Jose"
    }
}Code language: SQL (Structured Query Language) (sql)

In this example, we do not use the second argument, the indentation is four spaces per level.

Like the json() function, the json_pretty function validates the JSON value before formatting it:

SELECT json_pretty('{"name": "Alice" "age" : 25}');Code language: SQL (Structured Query Language) (sql)

Output:

Runtime error: malformed JSONCode language: SQL (Structured Query Language) (sql)

If you want the indentation to be one space per level, you can pass a space character to the second argument of the json_pretty() function like this:

SELECT json_pretty('{"name": "Alice"     ,"age" : 25 , "address": {"street": "101 N 1st Street", "city": "San Jose"} }',' ');Code language: SQL (Structured Query Language) (sql)

Output:

{
 "name": "Alice",
 "age": 25,
 "address": {
  "street": "101 N 1st Street",
  "city": "San Jose"
 }
}Code language: SQL (Structured Query Language) (sql)

Summary

• Use SQLite json_pretty() function to make a JSON value prettier by formatting it to make it more human-readable.