Subject: Re: inserting json content from a file into table column
To: Shashank Dutt Jha <shashank.dj@gmail.com>
References: 
 <CAKkUp9-iKD+e-=z0pQDB=K874aa6cRJdNdPPZws_SjO_ZSMQ_w@mail.gmail.com>
 <56BB55C1.4060305@aklaver.com>
 <CAKkUp98kBsogfZSjN140R98KO_2ew90KbzfqjRCBx_Ozhg_KuQ@mail.gmail.com>
Cc: pgsql-sql@postgresql.org
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56BB66A6.1070705@aklaver.com>
Date: Wed, 10 Feb 2016 08:34:46 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.5.1
MIME-Version: 1.0
In-Reply-To: 
 <CAKkUp98kBsogfZSjN140R98KO_2ew90KbzfqjRCBx_Ozhg_KuQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 02/10/2016 08:01 AM, Shashank Dutt Jha wrote:
> psql. PostgreSQL v9.5

A quick and dirty method:

aklaver@test=> create table json_test(id integer, json_fld jsonb);
CREATE TABLE

aklaver@test=> \e sample.json

\e opens an editor. Inside editor add INSERT statement to file:

  INSERT INTO json_test VALUES(1, '
{
     "glossary": {
         "title": "example glossary",
         "GlossDiv": {
             "title": "S",
             "GlossList": {
                 "GlossEntry": {
                     "ID": "SGML",
                     "SortAs": "SGML",
                     "GlossTerm": "Standard Generalized Markup Language",
                     "Acronym": "SGML",
                     "Abbrev": "ISO 8879:1986",
                     "GlossDef": {
                         "para": "A meta-markup language, used to create 
markup languages such as DocBook.",
                         "GlossSeeAlso": ["GML", "XML"]
                     },
                     "GlossSee": "markup"
                 }
             }
         }
     }
}');


aklaver@test=> \x
Expanded display is on.
aklaver@test=> select * from json_test;
-[ RECORD 1 
]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id       | 1
json_fld | {"glossary": {"title": "example glossary", "GlossDiv": 
{"title": "S", "GlossList": {"GlossEntry": {"ID": "SGML", "Abbrev": "ISO 
8879:1986", "SortAs": "SGML", "Acronym": "SGML", "GlossDef": {"para": "A 
meta-markup language, used to create markup languages such as DocBook.", 
"GlossSeeAlso": ["GML", "XML"]}, "GlossSee": "markup", "GlossTerm": 
"Standard Generalized Markup Language"}}}}}


Otherwise you will need to find a way to pass the file in from the 
shell. You are using Windows it seems and I am not familiar enough with 
it to offer any guidance on that topic.


>
> On Wed, Feb 10, 2016 at 8:52 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/10/2016 07:10 AM, Shashank Dutt Jha wrote:
>
>         I have .json file.C:/ a.json
>         Table with column 'food' of type jsonb
>
>         how to insert the content of a.json into column 'food'
>
>
>     What are you using as your client, for example psql, Java program,
>     Python program, etc.?
>
>     What version of Postgres are you using? In this case it probably
>     does not matter that much, but json/jsonb has changed a good deal
>     over recent versions so it is nice to know what you are working with.
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql