missing something about json syntax

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

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

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Thu, Apr 20, 2023 at 7:47 PM Erik Wienhold <ewie@ewie.name> wrote:

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 :