Data Types - PostgreSQL: Up and Running - page 101

you will find that Example 5-25 and Example 5-26 work largely without change for the
jsonb type—just replace the table name and change the json_array_length function
to the equivalent jsonb_array_length function.
In addition to the operators supported by json , jsonb has additional comparator oper‐
ators for equality ( = ), contains ( @> ), contained ( <@ ), key exists ( ? ), any of array of keys
exists ( ?| ), and all of array of keys exists ( ?& ).
So, for example, to list all families that have a member named Alex, use the contains
operator as demonstrated in Example 5-29 .
Example 5-29. jsonb contains operator
SELECT profile ->> 'name' As family
FROM families_b
WHERE profile @> '{"members":[{"member":{"name":"Alex"} }]}' ;
family
-----
Gomez
These additional operators provide very fast checks when you complement them with
a GIN index on the jsonb column:
CREATE INDEX idx_familes_jb_profile_gin ON families_b USING gin ( profile );
We don't have enough records in our puny table for the index to kick in, but for more
rows, you'd see that Example 5-29 utilizes the index.
XML
The XML data type, similar to JSON, is “controversial” in a relational database because
it violates principles of normalization. Nonetheless, all of the high-end relational data‐
bases products (IBM DB2, Oracle, SQL Server) support XML. PostgreSQL also jumped
on the bandwagon and offers plenty of functions to boot. We've authored many articles
on working with XML in PostgreSQL. (You can find these articles at PostgreSQL XML
Examples .) PostgreSQL comes packaged with functions for generating, manipulating,
and parsing XML data. These are outlined in XML Functions . Unlike the jsonb type,
there is currently no direct index support for it. So you need to use functional indexes
to index subparts, similarly to what you can do with the plain json type.
Inserting XML Data
When you create a column of the xml data type, PostgreSQL automatically ensures that
only valid XML values populate the rows. This is what distinguishes an XML column
from just any text column. However, the XML is not validated against any Document
Type Definition (DTD) or XML Schema Definition (XSD), even if it is specified in the
XML document. To freshen up on what constitutes valid XML, Example 5-30 shows