0 like 0 dislike
292 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4.9k points)  

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

 

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
...