From: Erik Wienhold <ewie@ewie.name>
To: Marc Millas <marc.millas@mokadb.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: missing something about json syntax
Date: Thu, 20 Apr 2023 19:47:31 +0200 (CEST)
Message-ID: <422485092.653228.1682012851663@office.mailbox.org> (raw)
In-Reply-To: <CADX_1aaysDhtyAia0_EN5yhLWPYU+5y0jo_+5eoXCFMuVKVf2w@mail.gmail.com>
References: <CADX_1aaysDhtyAia0_EN5yhLWPYU+5y0jo_+5eoXCFMuVKVf2w@mail.gmail.com>
> On 20/04/2023 18:35 CEST Marc Millas <marc.millas@mokadb.com> wrote:
>
> Hi,
>
> postgres 15
>
> looks Iike I am missing something, maybe obvious :-(
> In a table with a json column (_data) if I ask psql to select _data from
> mytable with a where clause to get only one line,, I get something beginning
> by
> {"time":"2023-04-19T16:28:01.19780551+02:00","stream":"stderr","_p":"F","log":"{\"level\":\"info\",\"ts\":\"2023-04-19T14:28:01Z\",\"logger\":\"_audit\",\"msg\":\"record\",\"logging_pod\":\"cluster-pgsql\",\"record\":{\"log_time\":\"2023-04-19 14:28:01.197 UTC\",\
> etc...
The value of property "log" is a string, not an object. Notice the escaped
double quotes (\").
> if I create table anothertable as select _data as _data from mytable, it
> creates and feed that new table with all the appropriate data, and when I ask
> psql \d anothertable it says that its a table with a json column.named _data.
> fine !
>
> now if I select json_object_keys(_data) from mytable, I get a list of tags.
> time, stream, _p, log, fine.
> now, if i select json_object_keys(_data) from anothettable, I get an error:
> cannot call json_objet_keys on a scalar..
>
> ???
> both columns are fed and of type json. and postgres didn't throw any error
> feeding them.
> if I create a table with a jsonb column and feed it with the anothertable json
> column, same, fine... but still unusable.
>
> and unusable with all the other ways I did try, like simply
> select _data->'log'->>'level' from mytable, or
> select _data->'level' from anothertable
>
> sure if I look at the json field one is showed { "tag": "value", ...
> and the other is showed "{\"tag\":\"value\", ...
You executed
create table anothertable as select _data->'log' as _data from mytable;
and not
create table anothertable as select _data as _data from mytable;
So you end up with the scalar value of property "log" in anothertable._data.
> not the very same
>
> so 2 questions:
> 1) how postgres can feed a json or jsonb column and CANNOT use the values in
> it ??
> 2) how to "transform" the inappropriate json into a usable one ?
>
> of course, if what I am missing is very obvious, I apologize...
Get the log value with operator ->> and cast the returned text to json:
select (_data->>'log')::json->'level' from mytable;
--
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, marc.millas@mokadb.com
Subject: Re: missing something about json syntax
In-Reply-To: <422485092.653228.1682012851663@office.mailbox.org>
References: <CADX_1aaysDhtyAia0_EN5yhLWPYU+5y0jo_+5eoXCFMuVKVf2w@mail.gmail.com> <422485092.653228.1682012851663@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox