Data Types - PostgreSQL: Up and Running - page 98

Return the member/name path element as text.
Expand the array of members into individual JSON objects.
Get list of members as a new JSON object.
The ->> and #>> operators are shorthand for json_extract_path_text . The #>> takes
a path array. Example 5-25 rewrites Example 5-24 using these symbolic operators.
Example 5-25. Extract path equivalent operators
SELECT profile ->> 'name' As family , json_array_elements (( profile -> 'members' )) #>>
'{member,name}' :: text [] As member
FROM families_j ;
A companion function, json_extract_path , which can be represented as -> and #> ,
returns a JSON object representing the subelement. This function is particularly useful
for passing complex elements such as members into another function for further ma‐
nipulation.
Although there weren't any functions for easily querying JSON data in version 9.2, you
can accomplish much of what is native in version 9.3 by writing PL/V8 functions. We
demonstrate how to create a jQuery-like selector function in Using PLV8 to Build JSON
Selectors .
Several functions are available for working with arrays in a JSON structure. You already
saw the use of json_array_elements in Example 5-25 . In addition to json_array_el
ements , you can use json_array_length to get a count of elements and -> with an index
position to return specific index element. You can chain operators together to burrow
into the JSON object, as shown in Example 5-26 .
Example 5-26. Query subelements of members
SELECT id , json_array_length ( profile -> 'members' ) As numero , profile -> 'members' -
> 0 #>> '{member,name}' :: text [] As primero
FROM families_j ;
id | numero | primero
---+--------+--------
1 | 4 | Alex
Example 5-26 uses two versions of the -> operator. The -> operator always returns a
json or jsonb object, but it takes as a second argument either a text field (shorthand
for json_object_field ) or an integer (shorthand for json_array_element ). So
profile->'members' returns the JSON object's members field, which happens to be a
JSON array. ->0 works against a JSON array field and returns the first element. In our
example, it returns the first member of our family. #>>'{member,name}'::text[] is
shorthand for json_extract_path_text , so it returns the text value corresponding to
the JSON member/name node of our first member. Note how we can seamlessly chain