pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedmissing something about json syntax
5+ messages / 3 participants
[nested] [flat]
* missing something about json syntax
@ 2023-04-20 16:35 Marc Millas <marc.millas@mokadb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Marc Millas @ 2023-04-20 16:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
www.mokadb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: missing something about json syntax
@ 2023-04-20 17:47 Erik Wienhold <ewie@ewie.name>
parent: Marc Millas <marc.millas@mokadb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Erik Wienhold @ 2023-04-20 17:47 UTC (permalink / raw)
To: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: missing something about json syntax
@ 2023-04-20 19:53 Marc Millas <marc.millas@mokadb.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 5+ messages in thread
From: Marc Millas @ 2023-04-20 19:53 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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:
> > 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: missing something about json syntax
@ 2023-04-20 20:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Millas <marc.millas@mokadb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-04-20 20:41 UTC (permalink / raw)
To: Marc Millas <marc.millas@mokadb.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: missing something about json syntax
@ 2023-04-21 10:22 Marc Millas <marc.millas@mokadb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Marc Millas @ 2023-04-21 10:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-04-21 10:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-20 16:35 missing something about json syntax Marc Millas <marc.millas@mokadb.com>
2023-04-20 17:47 ` Erik Wienhold <ewie@ewie.name>
2023-04-20 19:53 ` Marc Millas <marc.millas@mokadb.com>
2023-04-20 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-21 10:22 ` Marc Millas <marc.millas@mokadb.com>
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