Postgres Pro
Downloads
Home   >   mailing lists

Re: Extracting JSON data into columns - Mailing list pgsql-admin

From David G. Johnston
Subject Re: Extracting JSON data into columns
Date
Msg-id CAKFQuwYquKPjjdVsprxNGqUVSHCFj9M8V6tbLmRbyp+5VbYfsQ@mail.gmail.com
Whole thread Raw
In response to Extracting JSON data into columns  ("Ferrell, Denise SDC" <denise.ferrell@sdc-world.com>)
List pgsql-admin
Tree view
On Mon, Feb 22, 2016 at 7:48 AM, Ferrell, Denise SDC <denise.ferrell@sdc-world.com> wrote:
Running PostgreSQL 9.3 on Linux.
 
I'm looking for the syntax to extract JSON values into columns from one incoming column in file.
 
Thank you in advance.
Denise

​Syntax is well documented:

​http://www.postgresql.org/docs/9.3/interactive/functions-json.html

​Not tested...​

​WITH json_data (jd) AS (
VALUES ('{"key":"value", "key2":"value2"}'::json)
)​
​SELECT jd->>'key' AS col1, jd->>'key2' AS col2
FROM json_data​

Also useful is the:

json_populate_record(...)

function.

Try them and ask specific questions if you still have them.  Incorporating a self-contained example like mine lowers the bar for others to provide useful help.

Also consider upgrading to 9.5 if you expect to do heavy JSON work - features for this type have been being added at a good clip these past couple of years.

David J.



pgsql-admin by date:

Previous
From: "Ferrell, Denise SDC"
Date:
Subject: Extracting JSON data into columns
Next
From: Scott Marlowe
Date:
Subject: Re: [TIPS] Tuning PostgreSQL 9.2