Re: inserting json content from a file into table column - Mailing list pgsql-sql

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,
usedto 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