Re: inserting json content from a file into table column

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

A quick and dirty method:

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

aklaver(at)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(at)test=> \x
Expanded display is on.
aklaver(at)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com