agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedTurn a json column into a table
12+ messages / 4 participants
[nested] [flat]
* Turn a json column into a table
@ 2022-02-15 07:21 Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-15 07:21 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-15 07:52 Ion Alberdi <ion.alberdi@pricemoov.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Ion Alberdi @ 2022-02-15 07:52 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Re: Turn a json column into a table
@ 2022-02-15 08:11 Ion Alberdi <ion.alberdi@pricemoov.com>
parent: Ion Alberdi <ion.alberdi@pricemoov.com>
1 sibling, 1 reply; 12+ messages in thread
From: Ion Alberdi @ 2022-02-15 08:11 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi Karsten,
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
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Re: Turn a json column into a table
@ 2022-02-19 22:03 Shaozhong SHI <shishaozhong@gmail.com>
parent: Ion Alberdi <ion.alberdi@pricemoov.com>
1 sibling, 0 replies; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-19 22:03 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Re: Re: Turn a json column into a table
@ 2022-02-19 22:40 Shaozhong SHI <shishaozhong@gmail.com>
parent: Ion Alberdi <ion.alberdi@pricemoov.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-19 22:40 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-19 22:46 Rob Sargent <robjsargent@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-02-19 22:46 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-19 23:22 Shaozhong SHI <shishaozhong@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-19 23:22 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-19 23:54 Shaozhong SHI <shishaozhong@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-19 23:54 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-20 00:18 David G. Johnston <david.g.johnston@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2022-02-20 00:18 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-20 00:58 Shaozhong SHI <shishaozhong@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-20 00:58 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-20 01:08 David G. Johnston <david.g.johnston@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2022-02-20 01:08 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Turn a json column into a table
@ 2022-02-20 03:31 Shaozhong SHI <shishaozhong@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Shaozhong SHI @ 2022-02-20 03:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-02-20 03:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-15 07:21 Turn a json column into a table Shaozhong SHI <shishaozhong@gmail.com>
2022-02-15 07:52 ` Ion Alberdi <ion.alberdi@pricemoov.com>
2022-02-15 08:11 ` Ion Alberdi <ion.alberdi@pricemoov.com>
2022-02-19 22:40 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-02-19 22:46 ` Rob Sargent <robjsargent@gmail.com>
2022-02-19 23:22 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-02-19 23:54 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-02-20 00:18 ` David G. Johnston <david.g.johnston@gmail.com>
2022-02-20 00:58 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-02-20 01:08 ` David G. Johnston <david.g.johnston@gmail.com>
2022-02-20 03:31 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-02-19 22:03 ` Shaozhong SHI <shishaozhong@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox