Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain Creating and Inserting Tables in PostgreSQL Json Data Type.
First, let’s create a two-column data type called afirma.
1 2 3 4 | CREATE TABLE afirma ( id serial NOT NULL PRIMARY KEY, bilgi json NOT NULL ); |
After creating our table, we make inserts as follows.
1 2 3 4 5 6 7 | INSERT INTO afirma (bilgi) VALUES('{ "müşteri": "Ahmet aslan", "Satilanlar": {"Ürün": "su","fiyat": 1}}'); INSERT INTO afirma (bilgi) VALUES('{ "müşteri": "Mehmet Lale", "Satilanlar": {"Ürün": "araba parfümü","fiyat": 25}}'), ('{ "müşteri": "ayse ata", "Satilanlar": {"Ürün": "Parfüm","fiyat": 123}}'), ('{ "müşteri": "Faruk ERDEM", "Satilanlar": {"Ürün": "Oyuncak","fiyat": 1232}}'); |
After inserting our data, let’s see the data in the table.
Our data seems to be inserted properly, so how do we pull this data?
We will use two operators for this.
-> returns keys of json data.
->> returns value of json data.
Let’s list the Customer and the products sold to the customer in the information column.
1 2 3 | SELECT bilgi -> 'müşteri' AS müşteri ,bilgi -> 'Satilanlar' ->> 'Ürün' AS satilan_ürün FROM afirma |
If you want to use conditions in the query, you can use it as follows.
1 2 3 4 | SELECT bilgi -> 'müşteri' AS müşteri ,bilgi -> 'Satilanlar' ->> 'Ürün' AS satilan_ürün FROM afirma where bilgi -> 'Satilanlar' ->> 'Ürün'= 'su' |
You can use the example below to assign indexes to columns created in Json data type.
1 | CREATE INDEX afirma_IX2 ON afirma ((( bilgi -> 'Satilanlar' ->> 'Ürün')::text)); |
