How Can I Easily Import JSON Files into PostgreSQL Using psql?

Simplified PostgreSQL JSON data import

Importing JSON files into PostgreSQL usually requires complex SQL statements to extract data from JSON type columns into the actual table, which makes the process cumbersome. However, there is a simple way to simplify the process.

Using the command line tool psql, you can load JSON data directly into JSON columns without explicitly embedding SQL. The key is to use backticks to assign JSON to psql variables.

Suppose there is a JSON file named customers.json with the following content:

<code>[
  {
    "id": 23635,
    "name": "Jerry Green",
    "comment": "Imported from facebook."
  },
  {
    "id": 23636,
    "name": "John Wayne",
    "comment": "Imported from facebook."
  }
]</code>

To import this data into a table named customers without any SQL interaction, just execute the following command:

<code>\set content `cat customers.json`
create temp table t ( j jsonb );
insert into t values (:'content');
select * from t;</code>

This will load the data into a temporary table. You can then perform operations directly on the data, such as extracting the value of the "dog" key:

<code>select :'content'::jsonb -> 'dog';</code>

Using psql variable interpolation technology to simplify the JSON import process, allowing you to easily insert and manipulate JSON data without complex SQL statements.

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