Storing JSON data in Postgres

1. Storing JSON data in Postgres

Now that we've covered the basics of Postgres JSON, we're ready to take a look at how this data is stored. First up, we'll take a look at INSERTing and COPYing JSON data to a Postgres table.

2. INSERT INTO and COPY JSON records to Postgres

With the INSERT INTO keywords, a single row (or set of rows) can be inserted into a Postgres table. This is done by specifying a table, set of columns, and corresponding values to be inserted into the table. In this example, the parent-meta column is of type JSON. The value being inserted resembles a Python dictionary, or document, made up of key-value pairs and denoted with braces. Note that the double quotes are escaped with back-slashes. Postgres offers its COPY ... FROM syntax, to populate a table with the contents of a file. The COPY keyword takes a table name, and the file name follows the FROM keyword. Additional configuration details can be provided, including the DELIMITER, type of file, and presence of a header. When using COPY ... FROM, it's important to keep in mind that the columns in the file must exactly match the columns in the destination table. This means the number of columns, as well as the type of each column in the file must match the table. Loading document data using Postgres JSON can be tricky. It's common to forget escape characters, or other small, formatting details when working with semi-structured data.

3. Turning tabular data into JSON format

Sometimes, we'll want to convert data from a Postgres table into a document format. To do that, Postgres offers the row_to_json function. This function takes a row of data and returns a JSON object, which can then be stored as a column in a table. To convert the school, age, and address columns to a single column of type JSON, pass the school, age, and address columns to the row function within the row-to-json function. This returns a single column, with each record containing three key-value pairs. When using the row-to-json function in conjunction with the row function, keep in mind that the keys that are created will be generic and will not match the column names of the original data. In this case, the keys are f1, f2, and f3, which is typical behavior for the row-to-json function.

4. Extracting keys from JSON

It's important to understand the keys being used to store data when first exploring a table containing semi-structured data. Postgres provides a built-in json-object-keys function to extract the key from a column of type JSON. This function takes a column of type JSON, and returns all the keys being used to store data in this column. In our example, the parent-meta column is passed to the json-object-keys function, and the result is a table containing the keys used in each JSON object of every record in the parent-meta column. We can use the DISTINCT keyword in tandem with the json-object-keys functions to find only the unique keys being used in the parent-meta column. This returns only the distinct keys being used in the JSON objects in the parent-meta column and is a great first step in the initial discovery of document data.

5. Review

Fantastic - we've explored the basics of loading semi-structured document data to Postgres using INSERT INTO and COPY ... FROM. We also explored creating columns of type JSON with the row_to_json and row functions. Finally, we leveraged the json_object_keys function to find the keys present in all records of a column containing document data. Take a second to pause the video and explore the commands that you'll use in the following exercises.

6. Let's practice!

Time to practice your newfound skills with a few hands-on exercises. Best of luck!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.