• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL Json Data Type

        In this tutorial, i will explain you how to work with PostgreSQL JSON data type.
        what is JSON ?

        • It is one of the data type in postgres.
        • Json + PostgreSQL =NoSQL
        • JSON STANDS FOR JavaScript Object Notation
        • JSON data types stored value is valid according to the JSON rules.
        • JSON data types are for storing JSON data as multi-level, dynamically structured object graphs.
        • serialised object is stored in a text column. The json type takes care of deserialising it back to object graph while reading values from that column.
        • The main usage of JSON is to transport data between a server and web application. Unlike other formats, JSON is human-readable text.
        • PostgreSQL supports JSON data type since version 9.2.
        • It provides many functions and operators  for manipulating JSON data.

        There are two JSON data types: 
        1.json
        2.jsonb

        • The json data type stores an exact copy of the input text.
        • jsonb data is stored in a decomposed binary format
        • jsonb Insertion  makes it slightly slower to input due to added conversion overhead.
        • jsonb also supports indexing
        • JSONB does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

        PRACTICAL 1. CREATING SIMPLE JSON TABLE:

        postgres=# CREATE TABLE SALES (
         ID INT NOT NULL PRIMARY KEY,
         SALES_INFO json NOT NULL
        );

        From above  Sales  table consists of two columns:

        • The id column is the primary key column that identifies the sales id.
        • The sales_info column stores the data in the form of JSON data types.

        –Describe the sales table using \d

        postgres=# \d sales
               Table "public.sales"
           Column   |  Type   | Modifiers 
        ------------+---------+-----------
         id         | integer | not null
         sales_info | json    | not null
        Indexes:
            "sales_pkey" PRIMARY KEY, btree (id)

        PRACTICAL 2. INSERTING  JSON DATA ON SALES TABLE:

        INSERT INTO SALES VALUES
         (1,'{ "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}'
         );
        
        INSERT INTO SALES VALUES
         (2,'{ "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}'
         );
        
        INSERT INTO SALES VALUES
         (3,'{ "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}'
         );

        –List down the sales table

        postgres=# select * from sales;
         id |                                    sales_info                                    
        ----+----------------------------------------------------------------------------------
          1 | { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
          2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
          3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
        (3 rows)

        –list down the json column only

        postgres=# select sales_info from sales;
                                            sales_info                                    
        ----------------------------------------------------------------------------------
         { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
         { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
         { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
        (3 rows)

        –List down the id column only for understanding purposes.

        postgres=# select id from sales;
         id 
        ----
          1
          2
          3
        (3 rows)

        Deleting json objects:

        postgres=# select * from sales;
         id |                                  sales_info                                   
        ----+-------------------------------------------------------------------------------
          2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
          3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
          4 | { "customer": "junaith", "PRODUCTS": {"product_name": "pen","total_item": 8}}
          7 | { "customer": "daniel", "PRODUCTS": {"product_name": "car","total_item": 8}}
          8 | { "customer": "daniel", "PRODUCTS": {"product_name": "car","total_item": 8}}
        (5 rows)
        
        postgres=# delete from sales where sales_info ->'PRODUCTS'->>'total_item'='8';
        DELETE 3
        postgres=# select * from sales;                                               
         id |                                  sales_info                                   
        ----+-------------------------------------------------------------------------------
          2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
          3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
        (2 rows)

        PRACTICAL 3. HOW TO QUERYING & FILTERING JSON DATA:
        the two operators -> and ->> to help you query JSON data.

        • -> will return the attribute as a JSON object key(original JSON type).
        • ->> will return the property as integer or text (the parsed form of the attribute).

        –See the below example first two query  did’nt specify column name that is why it showing  ?column? after that i specified the column name as customer_name

        postgres=# select sales_info -> 'customer' from sales;
         ?column? 
        ----------
         "NIJAM"
         "ABU"
         "UMAR"
        (3 rows)
        
        postgres=# select sales_info ->> 'customer' from sales;
         ?column? 
        ----------
         NIJAM
         ABU
         UMAR
        (3 rows)
        
        postgres=# select sales_info ->> 'customer' as customer_name from sales;
         customer_name 
        ---------------
         NIJAM
         ABU
         UMAR
        (3 rows)
        
        postgres=# select sales_info -> 'PRODUCTS'  from sales;                 
                           ?column?                   
        ----------------------------------------------
         {"product_name": "choclate","total_item": 6}
         {"product_name": "badam","total_item": 5}
         {"product_name": "mobile","total_item": 1}
        • Using -> operator returns a JSON object, you can chain it with the operator ->> to retrieve a specific node.
        • JSON IS CASESENSITIVE DEFAULTY, from below example first i try to retrieve the data using “products”(lowercase) that is why it showed empty value after that i chaneged “PRODUCTS”(UPPER CASE)  then it showing values.
        postgres=# select sales_info -> 'products' ->>'product_name' from sales;
         ?column? 
        ----------
         
         
         
        (3 rows)
        
        
        postgres=# select sales_info -> 'PRODUCTS' ->'product_name' from sales;
          ?column?  
        ------------
         "choclate"
         "badam"
         "mobile"
        (3 rows)
        
        postgres=# select sales_info -> 'PRODUCTS' ->>'product_name' from sales;
         ?column? 
        ----------
         choclate
         badam
         mobile
        (3 rows

        FROM above example First  sales_info -> ‘PRODUCTS’  returns  as JSON objects. And then sales_info -> ‘PRODUCTS’ ->>’product_name’ returns all products as text.

        PRACTICAL 4. HOW TO USE WHERE CLAUSE ON JSON DATA:
        –Let us take the sales table for using WHERE CLAUSE

        postgres=# select * from sales;
         id |                                    sales_info                                    
        ----+----------------------------------------------------------------------------------
          1 | { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
          2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
          3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
        (3 rows)

        –To find out what are the products  bought customer UMAR I use the following query

        postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->>'PRODUCTS' ->>'product_name' 
        as name_of_product from sales where sales_info ->>'customer'='UMAR';
        ERROR:  operator does not exist: text ->> unknown
        LINE 1: ...stomer' as customer_name,sales_info ->>'PRODUCTS' ->>'produc...
                                                                     ^
        HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
        SOLUTION:
        • FIRST TIME WE SPECIFIED sales_info ->>’PRODUCTS’ ->>’product_name’ SO WE NEED TO GIVE  sales_info ->’PRODUCTS’ ->>’product_name’
        • postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->'PRODUCTS' ->>'product_name' 
          as name_of_product from sales where sales_info ->>'customer'='UMAR';
           customer_name | name_of_product 
          ---------------+-----------------
           UMAR          | mobile
          (1 row)
        • Defaulty FROM above example First  sales_info -> ‘PRODUCTS’  returns  as JSON objects. And then ‘PRODUCTS’ ->>’product_name’ returns all products as text.
        • when you call json nested data first you call like ->(json object key format)then Finally  you need to call ->>(text format or json object key format).
        • if you specify firstly json field as text(->>) format when calling nested node ,after that if you specify -> or ->> values  json will not know first text format(->>) value that is why it throw error.
        • JSON displayed  text format but actually json work as json object key(->) format .
        • postgres=# SELECT sales_info ->>'customer' as customer_name,sales_info ->'PRODUCTS' ->'product_name' 
          as name_of_product from sales where sales_info ->>'customer'='UMAR';
           customer_name | name_of_product 
          ---------------+-----------------
           UMAR          | "mobile"
          (1 row)
        • HERE i specified first json object(->) format finnaly i called json object format (->) this is not a problem.
        • other wise if you specify firstly as json text format (->>) finally if you give json object format (->) or  json text format (->>) what ever it is json will not know fist values surely it will throw error.
        PRACTICAL 5. HOW TO USE FUNCTION ON JSON DATA:

        json_each() function allows us to expand the outermost JSON object into a set of key-value pairs.

        postgres=# select sales_info from sales;
                                            sales_info                                    
        ----------------------------------------------------------------------------------
         { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 6}}
         { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
         { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
        (3 rows)

        –To get a set of keys in the outermost JSON object use function json_object_keys function

        postgres=# select json_each (sales_info) from sales;
                                    json_each                            
        -----------------------------------------------------------------
         (customer,"""NIJAM""")
         (PRODUCTS,"{""product_name"": ""choclate"",""total_item"": 6}")
         (customer,"""ABU""")
         (PRODUCTS,"{""product_name"": ""badam"",""total_item"": 5}")
         (customer,"""UMAR""")
         (PRODUCTS,"{""product_name"": ""mobile"",""total_item"": 1}")
        (6 rows)

        –If you want calculate  array size of json function

        select json_array_length('[1,2,3,4,5,6,7,8]');

        PRACTICAL 5. HOW TO CREATE ARRAY TABLE ON JSON DATA:
        Here we are creating array table using json data type and and also explained how to   retrieve the array data from json table and how to understand the array data.

        postgres=# create table TAB_ARRAY (id INT,BOOKS json);
        CREATE TABLE

        –Let us inserting array  values

        insert into tab_array values (1, '{
          "book_name": "story",
          "book_id": ["111", "232", "353", "484"] }' 
         );
        
        insert into tab_array values (2, '{
          "book_name": "the king",
          "book_id": ["1231", "78", "7", "9"] }' 
         );

         

        –List down the tab_array table

        postgres=# select * from tab_array;
         id |                    books                    
        ----+---------------------------------------------
          1 | {                                          +
            |   "book_name": "story",                    +
            |   "book_id": ["111", "232", "353", "484"] }
          2 | {                                          +
            |   "book_name": "the king",                 +
            |   "book_id": ["1231", "78", "7", "9"] }
        (2 rows)

        –filter the json data

        postgres=# select id, books->'book_id'->>2 from tab_array;
         id | ?column? 
        ----+----------
          1 | 353
          2 | 7
        (2 rows)
        
        postgres=# select id, books->'book_id'->>0 from tab_array where id=1;
         id | ?column? 
        ----+----------
          1 | 111
        (1 row)
        Note:Array start from 0 …N you will undestand json array below example
        [“111”, “232”, “353”, “484”]     ——>array values
            0        1         2        3         ——> array start from 0…3
        from above notes 
        if u give  where id=1 and books->’book_id’->>1 means it return 232
        if u give  where id=1 and books->’book_id’->>2 means it return 353
        if u give  where id=1 and books->’book_id’->>3 means it return 484
        if u give  where id=1 and books->’book_id’->>0 means it return 111
        PRACTICAL 6. HOW TO CREATE INDEX ON JSON TABLE:
        • index is fast fetching data without going full table scan.
        Here  i will explain how to create index on json data type table and how to  work index on json data type.

        –check the sales table for already having any index or not.

        postgres=# \d sales
               Table "public.sales"
           Column   |  Type   | Modifiers 
        ------------+---------+-----------
         id         | integer | not null
         sales_info | json    | not null
        Indexes:
            "sales_pkey" PRIMARY KEY, btree (id)
        
        
        postgres=# \di sales
        No matching relations found.

        –already we have sales table now let us go to create indicies on any (even nested) JSON field:

        create unique index ind_name
        on sales ((sales_info ->'PRODUCTS'->>'total_item'));

        –Now check the table index is properly created or not using \di,\dt,pg_indexes.

        postgres=# \di ind_name
                      List of relations
         Schema |   Name   | Type  |  Owner   | Table 
        --------+----------+-------+----------+-------
         public | ind_name | index | postgres | sales
        (1 row)
        
        
        
        Deleting json objectspostgres=# \d sales
               Table "public.sales"
           Column   |  Type   | Modifiers 
        ------------+---------+-----------
         id         | integer | not null
         sales_info | json    | not null
        Indexes:
            "sales_pkey" PRIMARY KEY, btree (id)
            "ind_name" UNIQUE, btree (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text))
        
        
        
        postgres=# select tablename,indexname,indexdef from pg_indexes where tablename='sales';
         tablename | indexname  |                                                    indexdef                       
                                      
        -----------+------------+-----------------------------------------------------------------------------------
        ------------------------------
         sales     | sales_pkey | CREATE UNIQUE INDEX sales_pkey ON sales USING btree (id)
         sales     | ind_name   | CREATE UNIQUE INDEX ind_name ON sales USING btree ((((sales_info -> 'PRODUCTS'::te
        xt) ->> 'total_item'::text)))
        (2 rows)

         From below example index is  properly working becouse we already created unique index on on json column of “total_item” unique index will not accept duplicate value if you try to insert duplicate values it will throw errors,i hope you understood it from below example.

        postgres=# select * from sales;
         id |                                    sales_info                                    
        ----+----------------------------------------------------------------------------------
          2 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 5}}
          3 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 1}}
          5 | { "customer": "NIJAM", "PRODUCTS": {"product_name": "choclate","total_item": 9}}
          1 | { "customer": "ABU", "PRODUCTS": {"product_name": "badam","total_item": 6}}
          4 | { "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 8}}
        (5 rows)
        
        
        postgres=# INSERT INTO SALES VALUES
         (9,'{ "customer": "UMAR", "PRODUCTS": {"product_name": "mobile","total_item": 8}}'
         );
        ERROR:  duplicate key value violates unique constraint "ind_name"
        DETAIL:  Key (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text))=(8) already exists.
        another example for json index is properly working or not

        HERE LET US consider  sales table having above 20000 json records for making practical json index , This way we can begin to see some performance implications when dealing with JSON data in Postgres, as well as how to solve them.

        SELECT count(*) FROM sales WHERE sales_info ->'PRODUCTS'->>'total_item'= '1';
        count
        -------
        4937 
        (1 row)

        –taking explain plan for without index

        postgres=# explain analyze SELECT sales_info ->>'customer' as customer_name from sales 
        where sales_info ->'PRODUCTS'->>'total_item'= '1';
        Aggregate (cost=335.12..335.13 rows=1 width=0) (actual time=4.421..4.421 rows=1 loops=1) -> Seq Scan on cards (cost=0.00..335.00 rows=50 width=0) (actual time=0.016..3.961 rows=4938 loops=1) 
            Filter: (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text) 
            Rows Removed by Filter: 5062 
        Total runtime: 4.475 ms$cd /db_data/data/pg_log
        $tail -f  postgresql-Tue.log

        Now, that wasn’t that slow of a query at 4.5ms, but let’s see if we can improve it.

        CREATE INDEX ind_name1 ON sales ((sales_info ->'PRODUCTS'->>'total_item'));

        If we run the same query which now has an index, we end up cutting the time in half.

        SELECT count(*) FROM sales WHERE sales_info ->'PRODUCTS'->>'total_item'= '1';
        count
        -------
        4937 
        (1 row)
        
        postgres=# explain analyze SELECT sales_info ->>'customer' as customer_name from sales 
        where sales_info ->'PRODUCTS'->>'total_item'= '1';
        Aggregate (cost=118.97..118.98 rows=1 width=0) (actual time=2.122..2.122 rows=1 loops=1) -> Bitmap Heap Scan on cards (cost=4.68..118.84 rows=50 width=0) (actual time=0.711..1.664 rows=4938 loops=1) 
            Recheck Cond: (((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text)  
            Heap Blocks: exact=185 
            -> Bitmap Index Scan on idxfinished (cost=0.00..4.66 rows=50 width=0) (actual time=0.671..0.671 rows=4938 loops=1) 
            Index Cond:(((sales_info -> 'PRODUCTS'::text) ->> 'total_item'::text) = '1'::text) 
        Total runtime: 2.175 ms

        Our query is now taking advantage of the ind_name1 index we created, and the query time has been approximately cut in half.

        Prev PostgreSQL Drop DOMAIN
        Next PostgreSQL Sequence

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal