Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppYNo-0003qg-Bz for pgsql-general@arkaria.postgresql.org; Thu, 20 Apr 2023 17:47:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppYNm-0001nI-HJ for pgsql-general@arkaria.postgresql.org; Thu, 20 Apr 2023 17:47:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppYNm-0001n8-6N for pgsql-general@lists.postgresql.org; Thu, 20 Apr 2023 17:47:46 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppYNg-000upO-8s for pgsql-general@lists.postgresql.org; Thu, 20 Apr 2023 17:47:45 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [10.196.197.2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4Q2Q900q7fz9sSw; Thu, 20 Apr 2023 19:47:32 +0200 (CEST) Date: Thu, 20 Apr 2023 19:47:31 +0200 (CEST) From: Erik Wienhold To: Marc Millas , "pgsql-general@lists.postgresql.org" Message-ID: <422485092.653228.1682012851663@office.mailbox.org> In-Reply-To: References: Subject: Re: missing something about json syntax MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 20/04/2023 18:35 CEST Marc Millas 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 beginn= ing > by > {"time":"2023-04-19T16:28:01.19780551+02:00","stream":"stderr","_p":"F","= log":"{\"level\":\"info\",\"ts\":\"2023-04-19T14:28:01Z\",\"logger\":\"_aud= it\",\"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=C2=A0as 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=C2=A0it says that its a table with a json column.nam= ed _data. > fine ! > > now if I select json_object_keys(_data) from mytable, I get a list of tag= s. > time, stream, _p, log, fine. > now, if i select json_object_keys(_data) from anothettable, I get an erro= r: > cannot call json_objet_keys on a scalar.. > > ??? > both columns are fed and of type json. and postgres didn't=C2=A0throw any= error > feeding them. > if I create a table with a jsonb column and feed it with the anothertable= =C2=A0json > 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=C2=A0one is showed { "tag": "value", ... > and the other is showed "{\"tag\":\"value\", ... You executed =09create table anothertable=C2=A0as select _data->'log' as _data from myta= ble; and not =09create table anothertable=C2=A0as 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=C2=A0json 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: =09select (_data->>'log')::json->'level' from mytable; -- Erik