How Can I Easily Import JSON Files into PostgreSQL Without Complex SQL?

Simplified PostgreSQL database JSON file import method

Importing JSON files into a PostgreSQL database is a common operation, but its complexity depends on the method used. This article introduces a method to easily import JSON data without complex SQL operations.

Suppose you have a JSON file called "customers.json" that contains a series of simple objects. The goal is to import these objects into a PostgreSQL table named "customers".

The traditional approach usually involves importing the JSON into an intermediate table as a json type column named "data", then using SQL to extract the value and insert it into the target table. This method is more cumbersome.

Fortunately, PostgreSQL provides an easier way. Using the command line tool psql, you can utilize backticks to load a JSON file into a psql variable. For example, assuming "test.json" contains your multi-row JSON data, the following SQL statement would be sufficient:

<code class="language-sql">sql> \set content `cat /tmp/test.json`
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;</code>

This method allows you to avoid explicitly embedding JSON data in SQL statements. Additionally, you can perform operations directly on imported data, as shown in the following example:

<code class="language-sql">sql> select :'content'::jsonb -> 'dog';</code>

This simple method simplifies the process of importing JSON data into a PostgreSQL database without the need for complex SQL operations.

The above is the detailed content of How Can I Easily Import JSON Files into PostgreSQL Without Complex SQL?. For more information, please follow other related articles on the PHP Chinese website!