Re: [SQL] JSON TO POSTGRE TABLE

[Shujie Shang <sshang@xxxxxxxxxx>]

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@xxxxxxxxx> wrote:

On Mon, Mar 30, 2015 at 6:54 PM, Kavi <kaviraj125@xxxxxxxxx> 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