Using JSON Data Types in PostgreSQL

While arrays can be useful for storing a list of values in a single field, sometimes our data structures can be complex. For example, we might want to store multiple values of different types in a single field, and we might want data to be keyed with labels rather than stored sequentially. These are common issues with log-level data, as well as alternative data.

JavaScript Object Notation (JSON) is an open standard text format for storing data of varying complexity. It can be used to represent just about anything. Similar to how a database table has column names, JSON data has keys. We can use JSON to represent a record from our customers database easily by storing column names as keys and row values as values. The row_to_json function transforms rows to JSON:

SELECT row_to_json(c) FROM customers c limit 1;

Here is the output of the preceding query:

Figure 5.11: A row converted to JSON

This is a little hard to read...