Postgres Pro
Downloads
Home   >   mailing lists

Thread: JSON TO POSTGRE TABLE

JSON TO POSTGRE TABLE

From
Kavi
Date:
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

--
Thanking You
Regards
Mr.Kavi R. Pachkawade

Re: JSON TO POSTGRE TABLE

From
Michael Paquier
Date:


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

Re: JSON TO POSTGRE TABLE

From
Michael Paquier
Date:


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

Re: [SQL] JSON TO POSTGRE TABLE

From
Shujie Shang
Date:
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:


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

Re: [NOVICE] [SQL] JSON TO POSTGRE TABLE

From
Felipe Santos
Date:



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.


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