JSON TO POSTGRE TABLE

On Mon, Mar 30, 2015 at 6:54 PM, Kavi <kaviraj125@gmail.com> wrote:

I sample data as below-

{
"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"
}
}
}
}
}

how can i inerst these json data in postgre tables.

.ie I want to convert json into sql as per the field

This may have been better asked in pgsql-novice I guess...

JSON is a data type:
http://www.postgresql.org/docs/devel/static/datatype-json.html
So what you need to do is at least to create a table with a column having
as data type JSON, and then insert data into it:
=# CREATE TABLE json_tab (data json);
CREATE TABLE
=# INSERT INTO json_tab VALUES ('{"key":"value","key2":"value2"}');
INSERT 0 1
=# select * from json_tab ;
data
---------------------------------
{"key":"value","key2":"value2"}
(1 row)
--
Michael

On Mon, Mar 30, 2015 at 7:11 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

On Mon, Mar 30, 2015 at 6:54 PM, Kavi <kaviraj125@gmail.com> wrote:

I sample data as below-

{
"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"
}
}
}
}
}

how can i inerst these json data in postgre tables.

.ie I want to convert json into sql as per the field

This may have been better asked in pgsql-novice I guess...

Oh, it was the case. You should avoid cross-posting into several lists at
the same time...
--
Michael

Hi,
I want to ask a question about json index.
Can I make a index on a field of a json column?
e.g, for table 'json_tab' you given, can I run something like "create index
'json_index' on json_tab::key"?

On Mon, Mar 30, 2015 at 6:11 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

Hi,

I want to ask a question about json index.
Can I make a index on a field of a json column?
e.g, for table 'json_tab' you given, can I run something like "create
index 'json_index' on json_tab::key"?

Yes.

Here it is an example:
http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3

Also, in 9.4 we got JSONB data type, which may enhance JSON fields
performance.