From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "Ferrell, Denise SDC" <denise(dot)ferrell(at)sdc-world(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Extracting JSON data into columns |
Date: | 2016-02-22 22:20:08 |
Message-ID: | CAKFQuwYquKPjjdVsprxNGqUVSHCFj9M8V6tbLmRbyp+5VbYfsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Feb 22, 2016 at 7:48 AM, Ferrell, Denise SDC <
denise(dot)ferrell(at)sdc-world(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2016-02-23 03:41:38 | Re: [TIPS] Tuning PostgreSQL 9.2 |
Previous Message | Dorian Machado | 2016-02-22 21:29:33 | Re: [TIPS] Tuning PostgreSQL 9.2 |