Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

HOW TO CREATE ARRAY ON JSON TABLE

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

7 Steps to configure BDR replication in postgresql

Postgresql Database Tutorial android app