agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedJsonb column
3+ messages / 2 participants
[nested] [flat]
* Jsonb column
@ 2019-03-12 13:33 MICHAEL LAZLO <giantmetfan@comcast.net>
2019-03-12 14:27 ` Re: Jsonb column Alexandru Lazarev <alexandru.lazarev@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: MICHAEL LAZLO @ 2019-03-12 13:33 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Jsonb column
2019-03-12 13:33 Jsonb column MICHAEL LAZLO <giantmetfan@comcast.net>
@ 2019-03-12 14:27 ` Alexandru Lazarev <alexandru.lazarev@gmail.com>
2019-03-12 16:30 ` Re: Jsonb column MICHAEL LAZLO <giantmetfan@comcast.net>
0 siblings, 1 reply; 3+ messages in thread
From: Alexandru Lazarev @ 2019-03-12 14:27 UTC (permalink / raw)
To: MICHAEL LAZLO <giantmetfan@comcast.net>; +Cc: pgsql-sql@lists.postgresql.org
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>
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?
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Jsonb column
2019-03-12 13:33 Jsonb column MICHAEL LAZLO <giantmetfan@comcast.net>
2019-03-12 14:27 ` Re: Jsonb column Alexandru Lazarev <alexandru.lazarev@gmail.com>
@ 2019-03-12 16:30 ` MICHAEL LAZLO <giantmetfan@comcast.net>
0 siblings, 0 replies; 3+ messages in thread
From: MICHAEL LAZLO @ 2019-03-12 16:30 UTC (permalink / raw)
To: Alexandru Lazarev <alexandru.lazarev@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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 <alexandru.lazarev@gmail.com> 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?
> >
> >
> > >
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-03-12 16:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-12 13:33 Jsonb column MICHAEL LAZLO <giantmetfan@comcast.net>
2019-03-12 14:27 ` Alexandru Lazarev <alexandru.lazarev@gmail.com>
2019-03-12 16:30 ` MICHAEL LAZLO <giantmetfan@comcast.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox