Re: Jsonb column

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 <[email protected]> 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 < [email protected] mailto:[email protected] > 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?
> > 
> > 
> >     >