A wide variety of JSON/JSONB functions are offered by PostgreSQL. These functions manipulate and modify the provided JSON/JSONB data and some are used to perform operations on them. In this post, the topic of discussion will be the jsonb_pretty() function. This function serves a unique purpose i.e. it properly adds the white spaces and indentation to the provided JSONB value so that it looks more readable. Let’s learn more about the jsonb_pretty() function and understand how is it used in PostgreSQL.
What is the Use of the jsonb_pretty Function in PostgreSQL?
The jsonb_pretty() function takes the JSONB value as a function parameter and returns a more readable, formatted, and understandable illustration of that JSONB object. The function adds necessary white spaces, new lines, and indentations to the provided data. The fundamental structure of the jsonb_pretty() function can be written as
jsonb_pretty(jsonb_val JSONB)
● The jsonb_pretty() function takes the JSONB value, which is to be formatted.
● The jsonb_pretty() function returns a formatted version of that provided value in the TEXT type.
We’ll now assess how the jsonb_pretty() function adds the formatting to the JSONB values, using the examples.
Example 1: Understanding the jsonb_pretty() Function
Consider the following simple query to understand how the jsonb_pretty() function will format the provided JSONB value (i.e. the JSONB array in this example)
SELECT jsonb_pretty('["Alex", "Peter", "Kate", "Oliver"]');We have provided a JSONB array with some strings/names to the jsonb_pretty() function. The function will implement proper formatting on the provided JSONB value like this:
The jsonb_pretty() function has added proper indentations, spacings, and new lines to the provided JSONB array. Each new element/value is placed in a new line with proper indentation. The JSONB object has now become more readable and understandable.
Note that the ”+” sign shows the insertion of a new line.
This was a simple case now we’ll see how the jsonb_pretty() function responds if we provide a nested array.
Example 2: Understanding the jsonb_pretty() Function
We’ll notice how the jsonb_pretty() function responds to the nested array using the following query:
SELECT jsonb_pretty('["Alex", "Peter", ["Kate", "Oliver"]]');We have provided the nested array to the function. We’ll now observe the clear difference in the formatting in this case.
The nested array has now become more readable and understandable as the jsonb_pretty() function has added proper indentation to the nested array. The new lines and white spaces are also properly used.
We will take another example into account to get more clarity.
Example 3: More About the jsonb_pretty() Function
Below is another example in which we provided a JSONB object having key-value pairs. We will see how the jsonb_pretty() function works on it.
SELECT jsonb_pretty('{"Alex":10, "Kate":9, "Peter":[8,7]}');In the above query, we have provided the keys and values to the function. The following is the output for this query.
We can see that the objects are properly indented. Each new element/value is placed in a new line. In the third element, there are two values. So these 2 values are again placed in separate new lines with proper indentation.
The jsonb_pretty() function returns the JSONB values in a beautified format, which makes them more readable and easy to understand for the user.
Conclusion
The jsonb_pretty() function returns the beautified version of the provided JSONB value. This function applies proper formatting to the given value. The jsonb_pretty() function takes in the JSONB object, adds proper white spaces, new lines, and indentation to that value, and returns it in the TEXT type. This tutorial demonstrated how the jsonb_pretty() function works on different JSONB values in PostgreSQL using examples.
