Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h3kIV-0003y5-8J for pgsql-sql@arkaria.postgresql.org; Tue, 12 Mar 2019 16:30:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h3kIS-00005f-Um for pgsql-sql@arkaria.postgresql.org; Tue, 12 Mar 2019 16:30:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h3kIR-0008SF-WD for pgsql-sql@lists.postgresql.org; Tue, 12 Mar 2019 16:30:32 +0000 Received: from resqmta-po-03v.sys.comcast.net ([2001:558:fe16:19:96:114:154:162]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h3kIG-0005Xc-1L for pgsql-sql@lists.postgresql.org; Tue, 12 Mar 2019 16:30:30 +0000 Received: from resomta-po-04v.sys.comcast.net ([96.114.154.228]) by resqmta-po-03v.sys.comcast.net with ESMTP id 3iBnhCPoOZgSG3kIEhdrOM; Tue, 12 Mar 2019 16:30:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20161114; t=1552408218; bh=FPFv6cjDswOX7Dix9w2KEs0qT3TBskWsIGoXz0Cv6ok=; h=Received:Received:Date:From:Reply-To:To:Message-ID:Subject: MIME-Version:Content-Type; b=MJBvaFLy3c4w128fLMTYOA6Fm8n511Xg82P3PuMZA/SHm84unxUENgc74k/DsfAWE ZGcl7HTanmkcmgDpL/lv7gCXGS9Msfp/nWx3vkVJ7j78bGegQxkB/mf+2bxM+l+J2d 9BzhqFL1W71BoyLqI+G9mWPM3iCqevQ++KUuLlPNnI+T3lsUooIOQyI42hzeL2AUK9 aU96oxURCkc73m3MBceqVgEwrsBZ5twcaPbW6RLw7uSIrxDLwtfc/YnG2w+zPGV69l qfSIm0kdd2QMkZU9GcYNY+8jIg1dl0Eg0p1UNknq0bzzu3uJnf+TXsPbyY6hw9bkaZ uB0j/qmNb/kYQ== Received: from oxapp-hoa-65o.email.comcast.net ([96.116.225.25]) by resomta-po-04v.sys.comcast.net with ESMTPS id 3kIDhub0pEyiG3kIDhgLWh; Tue, 12 Mar 2019 16:30:17 +0000 X-Xfinity-VAAS: gggruggvucftvghtrhhoucdtuddrgedutddrgeekgdeltdcutefuodetggdotefrodftvfcurfhrohhfihhlvgemucevohhmtggrshhtqdftvghsihdpqfgfvfdppffquffrtefokffrnecuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenogfthfevqddquegrugfkmhhpohhrthgrnhgtvgculddvtddmnecujfgurhepfffhrhfvkfgjfhfugggtrfgkofhisegrtdgssgertdejnecuhfhrohhmpefokfevjfetgffnucfntegknffquceoghhirghnthhmvghtfhgrnhestghomhgtrghsthdrnhgvtheqnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenucfkphepleeirdduudeirddvvdehrddvhedpudehhedrudejkedrudektddrudegnecurfgrrhgrmhephhgvlhhopehogigrphhpqdhhohgrqdeihehordgvmhgrihhlrdgtohhmtggrshhtrdhnvghtpdhinhgvthepleeirdduudeirddvvdehrddvhedpmhgrihhlfhhrohhmpehgihgrnhhtmhgvthhfrghnsegtohhmtggrshhtrdhnvghtpdhrtghpthhtoheprghlvgigrghnughruhdrlhgriigrrhgvvhesghhmrghilhdrtghomhdprhgtphhtthhopehpghhsqhhlqdhsqhhlsehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedt X-Xfinity-VMeta: sc=??;st=legit Date: Tue, 12 Mar 2019 12:30:16 -0400 (EDT) From: MICHAEL LAZLO Reply-To: MICHAEL LAZLO To: Alexandru Lazarev Cc: pgsql-sql@lists.postgresql.org Message-ID: <1169265872.231129.1552408217005@connect.xfinity.com> In-Reply-To: References: <1310583700.228697.1552397611933@connect.xfinity.com> Subject: Re: Jsonb column MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_231128_1348965462.1552408216997" X-Priority: 3 Importance: Medium X-Mailer: Open-Xchange Mailer v7.8.4-Rev38 X-Originating-IP: ::ffff:155.178.180.14 X-Originating-Client: open-xchange-appsuite List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_231128_1348965462.1552408216997 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Ah, Now I see what is going on. SETOF is like a NESTED TABLE in Oracle in the sense that each function is returning a table structure (SETOF). Because I used FETCH FIRST 1 ROWS ONLY the query is technically only returning 1 row from the output AFTER the rest of the query runs. It is generating output, then the limiting takes place, so the first row returned from json_object_keys & jsonb_each return only 1 row each also. So if I change my query to, because the first row returned has 20 keys: SELECT message_time ,airport ,jsonb_pretty(properties) as properties ,jsonb_object_keys(properties) as keys ,jsonb_each(properties) as json FROM JMS_MESSAGE FETCH FIRST 20 ROWS ONLY; it will return all the SETOF rows back with repeating values from the JMS_MESSAGE table itself. Since I am just trying to get an idea of what key are stored in this table column, AND this table has over 288 million rows, I limited it to show just the first row. This is what caused my the confusion. So Thanks for the help. > On March 12, 2019 at 10:27 AM Alexandru Lazarev wrote: > > Because you obtain Cartesian JOIN between following data-sources: > - jsonb_pretty(properties) as properties, > - jsonb_object_keys(properties) as keys, which is SETOF all keys > - jsonb_each(properties) as json, which is SETOF jey-values from your column > properties::jsonb ? 'sendTo' as second, which is single TRUE/FALSE > > Then you do > "FETCH FIRST 1 ROWS ONLY;" > which will return only 1st row of all rows from above combinations > > Maybe You need something like? > SELECT jsonb_pretty( j.properties) as properties,jsonb_object_keys( j.properties) as keys,jsonb_each( j.properties) as json, j.properties::jsonb ? 'sendTo' as second FROM > (select * from JMS_MESSAGE FETCH FIRST 1 ROWS ONLY) j; > But for each key (from "jsonb_object_keys") and key-values row (from "jsonb_each") you'll have fetched each time full jsonb column pretty-printed and TRUE/FALSE from "?"-operator > > Regards, > AlexL > > On Tue, Mar 12, 2019 at 3:33 PM MICHAEL LAZLO < giantmetfan@comcast.net mailto:giantmetfan@comcast.net > wrote: > > > > I have a table that has a column, properties, of type jsonb. > > > > > > Based on documentation: > > > > > > https://www.postgresql.org/docs/10/functions-json.html > > > > > > To see all key / value pairs laid out as a table structure of columns, I can use function: > > > > > > jsonb_each(jsonb) which returns: > > > > > > "setof key text, value jsonb" > > > > > > Also if I want to see just the keys, I can use function: > > > > > > jsonb_object_keys(jsonb) which returns: > > > > > > "Returns set of keys in the outermost JSON object." > > > > > > , as per documentation. > > > > > > So why does my output look like this: > > > > > > SELECT jsonb_pretty(properties) as properties,jsonb_object_keys(properties) as keys,jsonb_each(properties) as json, properties::jsonb ? 'sendTo' as second FROM JMS_MESSAGE > > FETCH FIRST 1 ROWS ONLY; > > properties | keys | json | second > > -------------------------------------------------------------------------------+------+---------------+-------- > > { +| uri | (uri,"""/""") | t > > "uri": "/", +| | | > > "sendTo": "all", +| | | > > "tracon": "A80", +| | | > > "airport": "KATL", +| | | > > "msgType": "ML", +| | | > > "version": "4.0", +| | | > > "__HQ_CID": "5499f946-2ec6-11e9-916d-d566236f637b", +| | | > > "globalID": "41965859", +| | | > > "msgSeqID": "2530832", +| | | > > "timeline": "1550015275141 1550015275141 1550015275216", +| | | > > "timestamp": "2019-02-12T23:47:55.141Z", +| | | > > "req_method": "POST", +| | | > > "JMS_Solace_isXML": "true", +| | | > > "JMSXDeliveryCount": "1", +| | | > > "HQ_BRIDGE_MSG_ID_LIST": "ID:10.182.186.1188f32163b022a1ad0:16940996805",+| | | > > "JMS_Solace_DeliverToOne": "false", +| | | > > "solace_routing_dest_name": "SMES/all/ML/KATL/A80", +| | | > > "JMS_Solace_ElidingEligible": "false", +| | | > > "JMS_Solace_DeadMsgQueueEligible": "false", +| | | > > "Solace_JMS_Prop_IS_Reply_Message": "false" +| | | > > } | | | > > (1 row) > > > > > > The above jsonb_each and jsonb_object_keys both return only the first key. The boolean check if some key is in the jsonb column, (properties::jsonb ? 'sendTo'), returns true so I should see it in the returns for jsonb_each and json_object_keys. > > > > > > Anyone know why I am not seeing all the keys in this row column? > > > > > > > ------=_Part_231128_1348965462.1552408216997 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Ah,

Now I s= ee what is going on. SETOF is like a NESTED TABLE in Oracle in the sense th= at each function is returning a table structure (SETOF). Because I used


FETCH FIRST 1 ROWS= ONLY

the query is technically only returning 1 r= ow from the output AFTER the rest of the query runs. It is generating outpu= t, then the limiting takes place, so the first row returned from json_objec= t_keys & jsonb_each return only 1 row each also.


So if I change my query to, because th= e first row returned has 20 keys:


SELECT message_time
,airport
,jsonb_pretty(properti= es) as properties
,jsonb_object_keys(properties) as keys
,jsonb_each(= properties) as json
FROM JMS_MESSAGE
FETCH FIRST 20 ROWS ONLY;


it will return all the SET= OF rows back with repeating values from the JMS_MESSAGE table itself.
<= /p>


Since I am just tryi= ng to get an idea of what key are stored in this table column, AND this tab= le has over 288 million rows, I limited it to show just the first row. This= is what caused my the confusion.


So


Thanks for the help.


On March 12, 2019 at 10:27 AM Alexandru Lazarev <alexand= ru.lazarev@gmail.com> wrote:

Because you obta= in Cartesian JOIN between following data-sources:
- jsonb_pretty(p= roperties) as properties,
- jsonb_object_keys(properties) as keys= , which is SETOF all keys
- jsonb_each(properties) as json, = which is SETOF jey-values from your column
properties::jsonb= ? 'sendTo' as second, which is single TRUE/FALSE
Then you do
"FETCH FIRST 1 ROWS ONLY;" =
which will return only 1st row of all rows from above combin= ations

Maybe You need something like?
SE= LECT jsonb_pretty( j.properties) as properties,jsonb_objec= t_keys( j.properties) as keys,jsonb_each( j.properties) as json, j.properties::jsonb ? 'sendTo&= #39; as second FROM
(select * from JMS_MESSAGE FETCH FIRST 1= ROWS ONLY) j;
But for each key (from "jsonb= _object_keys") and key-values row (from "jsonb_each") you'l= l have fetched each time full jsonb column pretty-printed and TRUE/FALSE fr= om "?"-operator

Regards,
AlexL <= br>

On Tue, Mar 12, 2019 at 3:33 PM MICHAE= L LAZLO < giantmetfan@comcas= t.net> wrote:
I have a table that has a colum= n, properties, of type jsonb.


Based on documentation:
<= br>
https://www.postgresql.org/docs/10/func= tions-json.html


To see all key / value pairs laid out as= a table structure of columns, I can use function:


jsonb_eac= h(jsonb) which returns:


"setof key text, value jsonb"= ;


Also if I want to see just the keys, I can use function: <= br>

jsonb_object_keys(jsonb) which returns:


"R= eturns set of keys in the outermost JSON object."


, as p= er documentation.


So why does my output look like this:
=

SELECT jsonb_pretty(properties) as properties,jsonb_object_keys(= properties) as keys,jsonb_each(properties) as json, properties::jsonb ? = 9;sendTo' as second FROM JMS_MESSAGE
FETCH FIRST 1 ROWS ONLY;
=  properties | keys | json | second
-----------------------------= --------------------------------------------------+------+---------------+-= -------
 { +| uri | (uri,"""/""") | t  "uri": "/", +| | |
 "sendTo": = 4;all", +| | |
 "tracon": "A80", +| | |
=  "airport": "KATL", +| | |
 "msgType"= ;: "ML", +| | |
 "version": "4.0", +| | |=
 "__HQ_CID": "5499f946-2ec6-11e9-916d-d566236f637b&#= 34;, +| | |
 "globalID": "41965859", +| | |
=  "msgSeqID": "2530832", +| | |
 "timelin= e": "1550015275141 1550015275141 1550015275216", +| | |
&#= 160;"timestamp": "2019-02-12T23:47:55.141Z", +| | |
&#= 160;"req_method": "POST", +| | |
 "JMS_Solace= _isXML": "true", +| | |
 "JMSXDeliveryCount":= "1", +| | |
 "HQ_BRIDGE_MSG_ID_LIST": "ID:10= .182.186.1188f32163b022a1ad0:16940996805",+| | |
 "JMS_So= lace_DeliverToOne": "false", +| | |
 "solace_rout= ing_dest_name": "SMES/all/ML/KATL/A80", +| | |
 "= JMS_Solace_ElidingEligible": "false", +| | |
 "JM= S_Solace_DeadMsgQueueEligible": "false", +| | |
 "= ;Solace_JMS_Prop_IS_Reply_Message": "false" +| | |
 }= | | |
(1 row)


The above jsonb_each and jsonb_object_ke= ys both return only the first key. The boolean check if some key is in the = jsonb column, (properties::jsonb ? 'sendTo'), returns true so I sho= uld see it in the returns for jsonb_each and json_object_keys.

Anyone know why I am not seeing all the keys in this row column?
=20 ------=_Part_231128_1348965462.1552408216997--