| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Want to json_populate_record AND save/link the source JSON text |
| Date: | 2016-07-29 20:17:52 |
| Message-ID: | CAKFQuwZB65vtZ0heeEORHWb-G9ttVGYSgAkv+CotwHKKYiqb6A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Given:
CREATE TABLE jsontbl ( id int, label text, json_obj json);
I need the create two rows from the following JSON, with the json_obj in
each row equal to the text/json object in the content
1,'one',{"id":1,"label":"one"}
2,'two',{"id":2,"label":"two"}
I'd like to not have to hard-code the columns ...
I've dead-ended at:
SELECT jpr.*, json_src
FROM json_array_elements($json$[ { "id":1, "label":"one" }, { "id":2,
"label":"two" } ]$json$::json) jae (json_src),
LATERAL json_populate_record(null::jsontbl, json_src) jpr
Which gets me all of the content but I'm stumped at how to send this
through "INSERT INTO jsobtbl" without knowing the columns and without
removing the ".*"
Looking for inspiration here...
It would nice to simply have a function defined:
RETURNS TABLE (out_rec anyelement, src_json json)
I can easily live with the treatment of out_rec as a composite type.
Thanks!
David J.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Condor | 2016-07-29 20:18:54 | Re: Uber migrated from Postgres to MySQL |
| Previous Message | D'Arcy J.M. Cain | 2016-07-29 20:17:08 | Re: Allowing multiple versions of PG under NetBSD |