How to Query Postgres Insert into a JSON Column
Introduction
Standing for JavaScript Object Notation, JSON is an open-standard language format consisting of key-value pairs that can store and transport data. One of the JSON data types PostgreSQL provides for is stored as text, however, the data must be in a valid data type column JSON format in order to store the values in a PostgreSQL table. This tutorial will provide explanations and examples on how to query Postgres to insert into a JSON column in a PostgreSQL table.
Prerequisites
- PostgreSQL must be properly installed, configured and working on the local device in order to query Postgres to insert into a JSON column in a PostgreSQL table.
Open the psql command line console
Before Postgres can be used to insert data into a JSON column, the psql
interactive shell, used to execute the PostgreSQL commands, must first be accessed with the following command:
1 | sudo -u postgres psql |
Now enter the sudo root password to complete access to Postgres.
Create a database in PostgreSQL
A database can be created once psql
is accessed in the terminal. If a database is already set up, skip this step and proceed to the next section on creating a table with a JSON column.
Execute the following command to create a database in PostgreSQL:
1 | CREATE DATABASE dbname; |
NOTE: Be sure to provide the newly created database with a meaningful name.
- Use the
\l
command to search for known databases in Postgres. Then execute the\c
command followed by the database name to complete the connection.
Create a table in PostgreSQL
Execute the following syntax to create a table with a JSON column in PostgreSQL:
1 2 3 4 5 | CREATE TABLE sample( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL, more_info JSON); |
In the above example, assume the sample
table uses a Javascript array for the keys. The results should resemble the following:
1 | ["id", "name", "age", "more_info"]; |
Using the insert statement to insert a JSON document into the column
Use the following syntax to execute the INSERT
statement:
1 | INSERT INTO TABLE_NAME(col1, col2, col3) VALUES(val1, val2, val3); |
The results from the above example should produce the following:
1 2 3 4 | INSERT INTO sample(name, age, more_info) VALUES('Thomas', 22, '{"email": "thom22@gmail.com", "country": "US"}'), ('Jerry', 24, '{"email": "jerrybac@gmail.com", "country": "UK"}'), ('Annie', 21, '{"email": "anniehan@ymail.com", "country": "AUS"}'); |
Now execute the SELECT * FROM sample
command to display the JSON value. This should produce the following table:
1 2 3 4 5 | id | name | age | more_info ----+--------+-----+--------------------------------------------------- 1 | Thomas | 22 | {"email": "thom22@gmail.com", "country": "US"} 2 | Jerry | 24 | {"email": "jerrybac@gmail.com", "country": "UK"} 3 | Annie | 21 | {"email": "anniehan@ymail.com", "country": "AUS"} |
Insert into a JSON column using the JSON function in PostgreSQL
Following is an example showing how to insert JSON data into a column with a JSON data type using the json_populate_record
command:
1 2 3 4 5 6 7 8 9 10 11 | INSERT INTO sample SELECT id, name, age, more_info FROM json_populate_record (NULL::sample, '{ "id": "4", "name": "Hansel", "age": 25, "more_info": { "email": "hanselcrak54@gmail.com", "country": "PHIL" } }' ); |
Now executing the SELECT
statement again should produce the following results:
1 2 3 4 5 6 7 8 | id | name | age | more_info ----+--------+-----+--------------------------------------------------------- 1 | Thomas | 22 | {"email": "thom22@gmail.com", "country": "US"} 2 | Jerry | 24 | {"email": "jerrybac@gmail.com", "country": "UK"} 3 | Annie | 21 | {"email": "anniehan@ymail.com", "country": "AUS"} 4 | Hansel | 25 | { + | | | "email": "hanselcrak54@gmail.com", "country": "PHIL"+ | | | } |
Note that the above INSERT
statement query has a double colon (::) indicating the transition of the data. The NULL
will then be transformed into data that can be used for executing the Postgres and json_populate_record
function.
Conclusion
This tutorial provided explanations and examples on how to query Postgres to insert into a JSON column in a PostgreSQL table. The tutorial explained how to open the psql command line console, create a database and a table in PostgreSQL. The article then covered how to use the INSERT statement and JSON function in PostgreSQL to insert a JSON document into the column along with working examples of the functions. Remember that the INSERT
statement query uses a double colon (::) to indicate the transition of data and the NULL
will then be transformed into data for executing the Postgres and json_populate_record
function.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started