めも

技術メモとその他

how to save [jsonb_pretty] format data

1)data is below

prototype=# select jsonb_pretty(memo) from foo

prototype-# where id = 1;

       jsonb_pretty        

---------------------------

 {                        +

     "name": "japan",     +

     "currency": "JPY",   +

     "population": "10000"+

 }

(1 row)

2)if you use copy to stdout, 

psql -d prototype -c "\COPY (select jsonb_pretty(memo) from foo where id=1) TO STDOUT" >test.json

result are...

cat test2.json

{\n    "name": "japan",\n    "currency": "JPY",\n    "population": "10000"\n}

3)make select script and call psql -c command like this

$ cat sel.sql

select jsonb_pretty(memo) from foo where id=1

and

psql -d prototype -f sel.sql | cat -n > sample.txt

 more sample.txt 

     1         jsonb_pretty        

     2  ---------------------------

     3   {                        +

     4       "name": "japan",     +

     5       "currency": "JPY",   +

     6       "population": "10000"+

     7   }

     8  (1 row)

     9