Jsonb column

From: MICHAEL LAZLO <giantmetfan(at)comcast(dot)net>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Jsonb column
Date: 2019-03-12 13:33:31
Message-ID: 1310583700.228697.1552397611933@connect.xfinity.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Alexandru Lazarev 2019-03-12 14:27:22 Re: Jsonb column
Previous Message Tony Shelver 2019-03-07 05:44:49 Re: INSERT / UPDATE into 2 inner joined table simultaneously