How Can I Easily Import JSON Files into PostgreSQL without Using SQL?

Easily import JSON files to PostgreSQL without SQL

Many users often use complex methods when importing JSON files into PostgreSQL databases, such as using JSON type columns and SQL statements to operate. However, there is an easier solution.

JSONB variable in psql

PostgreSQL’s psql command line tool allows you to load JSON data into JSONB variables using backticks. You can then use this variable to insert data into the table.

For example, let’s say you have a JSON file called customers.json:

<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 you can do the following:

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

Data retrieval and manipulation

Once the data is imported, you can retrieve and manipulate it directly:

<code>select * from t;

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

These commands utilize JSONB variables to provide a more direct and convenient way to import JSON data into the PostgreSQL database.

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