Thread: Turn a json column into a table
There is a JSON column in a table. It contains key value pairs, just like a dictionary.
Keys are column names. Values are values contained in a row.
It looks like that each JSON object needs to be expanded into row values in different columns.
What is the best way to turn this JSON column into a data table?
Regards,
David
Hi David,
it seems (json_to_record, jsonb_to_record) may help in this use case.
https://www.postgresql.org/docs/9.4/functions-json.html
Regards
it seems (json_to_record, jsonb_to_record) may help in this use case.
https://www.postgresql.org/docs/9.4/functions-json.html
Regards
Le mar. 15 févr. 2022 à 08:21, Shaozhong SHI <shishaozhong@gmail.com> a écrit :
There is a JSON column in a table. It contains key value pairs, just like a dictionary.Keys are column names. Values are values contained in a row.It looks like that each JSON object needs to be expanded into row values in different columns.What is the best way to turn this JSON column into a data table?Regards,David
Hi Ion, > it seems (json_to_record, jsonb_to_record) may help in this use case. > https://www.postgresql.org/docs/9.4/functions-json.html json_to_record seems promising. Is there any excellent Postgresql example ? Karsten
Hi Karsten,
there seems to be an example in the archives
https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
there seems to be an example in the archives
https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
Le mar. 15 févr. 2022 à 09:09, Karsten Hilbert <Karsten.Hilbert@gmx.net> a écrit :
Hi Ion,
> it seems (json_to_record, jsonb_to_record) may help in this use case.
> https://www.postgresql.org/docs/9.4/functions-json.html
json_to_record seems promising. Is there any excellent Postgresql example ?
Karsten
Hi Ion, > there seems to be an example in the archives > https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us Many thanks ! I shall indeed go Read The Fine Manual now. In case I might have further questions I would like to come back, post my work and understanding, and ask for specific guidance on the aspects I can't fully solve myself. Thanks again, Karsten
On Tue, 15 Feb 2022 at 08:09, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
Hi Ion,
> it seems (json_to_record, jsonb_to_record) may help in this use case.
> https://www.postgresql.org/docs/9.4/functions-json.html
json_to_record seems promising. Is there any excellent Postgresql example ?
Karsten
Hi, Karsten
I tried the folowing;
SELECT x.* FROM structures.str_fts_compoundstructure t, json_to_record(properties) AS x(a_key text, b_key text, theme text, changetype text);
There are two similar fields, a_key, b_key ---identifiers.
But, a_key came out properly, but b_key came out all null. Frustrating.
Regards,
David
On Tue, 15 Feb 2022 at 08:37, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
Hi Ion,
> there seems to be an example in the archives
> https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
Many thanks ! I shall indeed go Read The Fine Manual now.
In case I might have further questions I would like to come back,
post my work and understanding, and ask for specific guidance on
the aspects I can't fully solve myself.
Thanks again,
Karsten
In the JSON column, one key can be seen present as other keys. But, when use json_object_keys, it did not turn up at all.
Is there a way to cast json column as text, and extract values from text?
Regards,
David
On 2/19/22 15:40, Shaozhong SHI wrote:
On Tue, 15 Feb 2022 at 08:37, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:Hi Ion,
> there seems to be an example in the archives
> https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
Many thanks ! I shall indeed go Read The Fine Manual now.
In case I might have further questions I would like to come back,
post my work and understanding, and ask for specific guidance on
the aspects I can't fully solve myself.
Thanks again,
Karsten
In the JSON column, one key can be seen present as other keys. But, when use json_object_keys, it did not turn up at all.Is there a way to cast json column as text, and extract values from text?
Nothing in https://www.postgresql.org/docs/14/functions-json.html helps?
On Sat, 19 Feb 2022 at 22:47, Rob Sargent <robjsargent@gmail.com> wrote:
On 2/19/22 15:40, Shaozhong SHI wrote:On Tue, 15 Feb 2022 at 08:37, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:Hi Ion,
> there seems to be an example in the archives
> https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
Many thanks ! I shall indeed go Read The Fine Manual now.
In case I might have further questions I would like to come back,
post my work and understanding, and ask for specific guidance on
the aspects I can't fully solve myself.
Thanks again,
Karsten
In the JSON column, one key can be seen present as other keys. But, when use json_object_keys, it did not turn up at all.Is there a way to cast json column as text, and extract values from text?
Nothing in https://www.postgresql.org/docs/14/functions-json.html helps?
That is very odd. That key and vale disappeared when the column casted as text.
Regards,
David
On Sat, 19 Feb 2022 at 23:22, Shaozhong SHI <shishaozhong@gmail.com> wrote:
On Sat, 19 Feb 2022 at 22:47, Rob Sargent <robjsargent@gmail.com> wrote:On 2/19/22 15:40, Shaozhong SHI wrote:On Tue, 15 Feb 2022 at 08:37, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:Hi Ion,
> there seems to be an example in the archives
> https://www.postgresql.org/message-id/20180526150323.GB28324%40momjian.us
Many thanks ! I shall indeed go Read The Fine Manual now.
In case I might have further questions I would like to come back,
post my work and understanding, and ask for specific guidance on
the aspects I can't fully solve myself.
Thanks again,
Karsten
In the JSON column, one key can be seen present as other keys. But, when use json_object_keys, it did not turn up at all.Is there a way to cast json column as text, and extract values from text?
Nothing in https://www.postgresql.org/docs/14/functions-json.html helps?That is very odd. That key and vale disappeared when the column casted as text.Regards,David
That is a very odd challenge. That key and value do appear in the data set but it will disappear whenever you do something with the json column.
Regards,
David
On Saturday, February 19, 2022, Shaozhong SHI <shishaozhong@gmail.com> wrote:
That is a very odd challenge. That key and value do appear in the data set but it will disappear whenever you do something with the json column.
Your observations of this sort aren’t that useful. What would be useful, like always, is a self-contained example.
David J.
On Sun, 20 Feb 2022 at 00:18, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, February 19, 2022, Shaozhong SHI <shishaozhong@gmail.com> wrote:That is a very odd challenge. That key and value do appear in the data set but it will disappear whenever you do something with the json column.Your observations of this sort aren’t that useful. What would be useful, like always, is a self-contained example.David J.
It is impossible to produce any self-contained example.
It can be seen but difficult to get a grip of it to show you. So odd.
Regards,
David
On Sat, Feb 19, 2022 at 5:58 PM Shaozhong SHI <shishaozhong@gmail.com> wrote:
It is impossible to produce any self-contained example.
We are talking about a data table and pure SQL, it is not impossible to produce a self-contained example. Even if said example fails to produce the desired behavior.
Another approach is to run psql and capture everything to a text file and share that - so even without the reproducer we can see exactly what you are seeing.
David J.
On Sunday, 20 February 2022, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sat, Feb 19, 2022 at 5:58 PM Shaozhong SHI <shishaozhong@gmail.com> wrote:It is impossible to produce any self-contained example.We are talking about a data table and pure SQL, it is not impossible to produce a self-contained example. Even if said example fails to produce the desired behavior.Another approach is to run psql and capture everything to a text file and share that - so even without the reproducer we can see exactly what you are seeing.David J.That did not work at all. I was given data sets to examine. These got loaded with ogr2ogr. But very strange behavior occur as I described in Posters.
Made copy back to disk. The key and value mentioned disappeared.
Regards, David
> It can be seen but difficult to get a grip of it to show you. Whatever "it" is. When something can be "seen" but not "shown" the natural reaction for bridging that gap would be to "record" so as to make "observing" _reproducible_, which technique may or may not be familiar to a scientist. Karsten