1. Querying JSON data using Postgres
We've covered the basics of document databases, and gotten our hands on a little bit of semi-structured data in Postgres. Now, let's take a closer look at querying JSON data using a few special operators.
2. Querying JSON data with Postgres
Postgres offers two powerful operators to query JSON objects within a column of type JSON or JSONB; the arrow operator and the double arrow operator. Both use similar syntax to extract the value stored at a key from the passed JSON object.
These two operators differ in the type of data that is returned upon execution. The single arrow operator returns the field as JSON, while the double arrow operator returns the field as TEXT. This is an important distinction when working with nested JSON objects, which we'll explore next.
In this example, the value stored at the "guardian" field is extracted from the parent-meta column using the single arrow operator and is returned as JSON. The double arrow operator is used to query the "status" field of the JSON objects in the parent-meta column, and a TEXT value is returned.
While subtle, the differences between these two operators is highlighted when working with nested data.
3. Querying nested JSON objects
When working with nested JSON objects, the arrow and double arrow operators can be used in tandem. First, the arrow operator is used to return the nested object in JSON format. Then, the double arrow operator is used to extract the text value of the specified field from the nested object.
In our example, the nested "jobs" object is first extracted from the parent-meta column using the arrow operator. Then, the "P1" and "P2" fields are queried, respectively, using the double arrow operator. In this case, one of the student's parent is a teacher, and the other stays at home. Postgres supports multiple layers of nesting, which is important, especially when working with complex, document datasets.
Later, we'll look at two additional operators that allow us to more easily work with nested JSON objects.
4. Querying JSON arrays
To extract individual values from a JSON array, the arrow and double arrow operators can again be used. This time, a JSON array precedes the operator, followed by an integer, specifying the index of the array to return. With Postgres JSON, array-indexing starts at 0.
Like before, the arrow operator returns the field at the specified index as JSON, while the double arrow operator returns the field as TEXT. In this example, we're querying the nested array to extract the first and second elements of the array stored via the "educations" key. This is another good example of using the single arrow and double arrow operators together to return nested data.
5. Finding the type of data store in JSON objects
Sometimes, we'll want to understand the type of data within a JSON object. To do that, Postgres provides the json-typeof function. This function returns the type of the outermost object that is passed to it and is typically used in tandem with the arrow operator. The json-typeof function returns one of six values: object, array, string, number, boolean, or null, and is especially useful when debugging or building queries.
In this example, the value stored at the "jobs" key is extracted from the parent_meta column and passed to the json-typeof function. The result is a column containing the data type of each value available at the "jobs" key. Since the double-arrow operator always returns text, it's typically not used with the json-typeof function.
6. Review
Pause the video to review the different tools we explored this video to extract information from document data with Postgres.
7. Let's practice!
Great work! Time to practice querying JSON data with Postgres with a few hands-on exercises. Best of luck!