1. Advanced Postgres JSON query techniques
Postgres provides a number of tools to process and query JSON data. In the final video of this chapter, we'll explore a few more of these tools.
2. Querying nested JSON data
Previously, to query nested data, we chained the arrow and double arrow operators together. While this will work, if data is nested deeply in an object, these statements can become quite long. To make querying nested data easier, Postgres provides the hash arrow and hash double arrow operators, along with the json-extract-path, and json-extract-path-text functions. Let's take a look at the hash arrow operators first.
3. #> and #>> operators
The hash arrow operator, which is made up of the hash character, as well as the greater than sign, is called on a column and takes a string array. In SQL, string arrays are denoted with brackets, and are separated by commas. This string array specifies the path to be queried within the JSON object. The hash arrow operator returns the field as JSON if the path exists, and will return null if the path doesn't exist. The hash double arrow operator uses the same syntax as the hash arrow operator, but instead, returns the field as text if the path exists.
Below are four uses of the hash arrow and hash double arrow operators to retrieve data from the table on the left. First, the value stored in the "jobs" field is queried and returned as JSON using the hash arrow operator. In the second example, the hash arrow operator is again used to return the nested value stored at the "P1" field in the "jobs" object as JSON. This is done by passing a second value in the string array. In the third example, we attempt to query the "income" field in the "jobs" object. However, since this value does not exist, null is returned. Finally, we used the hash double arrow operator to return the field stored in the "P2" field in the "jobs" object as text.
4. json_extract_path and json_extract_path_text
In addition to the hash arrow and double arrow operators, Postgres provides the json-extract-path and json-extract-path-text functions. These functions work like the hash arrow operators. A column is first passed to the function, followed by an arbitrary number of path elements. These path elements are then used to query the JSON object in the specified column and return the result as JSON. Like before, if the path does not exist, null will be returned. Like the json-extract-path function, the json-extract-path-text function takes a column and an arbitrary number of path elements, but instead returns the value stored at the specified path as text.
Here, we query the same fields as we did with the hash arrow operators, but this time, with the json-extract-path and json-extract-path-text functions. First, the object stored in the "jobs" field is returned as JSON. This is done by first passing the column name to the json-extract-path function, followed by the path element. In the next line, the nested "P1" field in the "jobs" object is queried. This time, the "jobs" and "P1" path elements are passed after the parent-meta column. In our third example, we attempt to return a value stored in the "income" field in the "jobs" object. However, since the field does not exist, null is returned. Finally, we used the hash double arrow operator to return the value stored at the "P2" field in the "jobs" object as text.
While the functionality is the same as the hash arrow operators, the json-extract-path and json-extract-path-text functions are quite useful, especially when working with heavily-nested JSON objects.
5. Review
Before we wrap up this chapter with a few exercises, pause the video to review the different tools we explored to extract information from document data with Postgres.
6. Let's practice!
Great work! Time to put your new skills to the test with a little bit of practice.