missing something about json syntax

Поиск
Список
Период
Сортировка
От Marc Millas
Тема missing something about json syntax
Дата
Msg-id CADX_1aaysDhtyAia0_EN5yhLWPYU+5y0jo_+5eoXCFMuVKVf2w@mail.gmail.com
обсуждение исходный текст
Ответы Re: missing something about json syntax  (Erik Wienhold <ewie@ewie.name>)
Список pgsql-general
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...

Marc MILLAS
Senior Architect
+33607850334

В списке pgsql-general по дате отправления:

Предыдущее
От: Bryn Llewellyn
Дата:
Сообщение: Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
Следующее
От: Erik Wienhold
Дата:
Сообщение: Re: missing something about json syntax