Обсуждение: missing something about json syntax
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...
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\", ...
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...
> 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.197UTC\",\ > 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
Thanks for your input.
select (_data->>'log')::json->'level' from mytable;
this does work.
but it doesnt explain how postgres is able to put a scalar in a json or jsonb column without pb:
I don't understand how this ('"{\"t\"}"') can be considered a valid enough json to be inserted in a json column
and at the same time invalid for all other json uses.
just like if postgres was allowing to insert things that are not of the column type
it's the first time I do encounter this kind of behaviour from postgres
On Thu, Apr 20, 2023 at 7:47 PM Erik Wienhold <ewie@ewie.name> wrote:
> 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
Marc Millas <marc.millas@mokadb.com> writes: > but it doesnt explain how postgres is able to put a scalar in a json or > jsonb column without pb: > I don't understand how this ('"{\"t\"}"') can be considered a valid enough > json to be inserted in a json column > and at the same time invalid for all other json uses. That's a bare string (it's not an object). That's valid JSON per RFC 7159: JSON can represent four primitive types (strings, numbers, booleans, and null) and two structured types (objects and arrays). ... A JSON text is a serialized value. Note that certain previous specifications of JSON constrained a JSON text to be an object or an array. However, there certainly are some operations that require the top-level value to be an object or array. regards, tom lane
Ok, thanks.
Le jeu. 20 avr. 2023 à 22:42, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Marc Millas <marc.millas@mokadb.com> writes:
> but it doesnt explain how postgres is able to put a scalar in a json or
> jsonb column without pb:
> I don't understand how this ('"{\"t\"}"') can be considered a valid enough
> json to be inserted in a json column
> and at the same time invalid for all other json uses.
That's a bare string (it's not an object). That's valid JSON per
RFC 7159:
JSON can represent four primitive types (strings, numbers, booleans,
and null) and two structured types (objects and arrays).
...
A JSON text is a serialized value. Note that certain previous
specifications of JSON constrained a JSON text to be an object or an
array.
However, there certainly are some operations that require the top-level
value to be an object or array.
regards, tom lane