官网参考资料

https://www.postgresql.org/docs/9.6/functions-json.html

查询相关语法

假设存了一个（Item表values列）jsonb数据如下

{
	"Text23": "",
	"Number1": 1,
	"Number2": 0,
	"ItemCode": "man-hour-2023",
	"LongText": "",
	"User3321": [{
		"label": "ds(dsdsdsdsds)",
		"value": "I2GgCsDDSM",
		"deleted": false,
		"enabled": true,
		"nickname": "ds",
		"username": "dsdsdsdsds"
	}],
	"assignee": [{
		"label": "Gitee_test(Gitee_test)",
		"value": "QWUqDqFNj8",
		"deleted": false,
		"enabled": true,
		"nickname": "Gitee_test",
		"username": "Gitee_test"
	}],
	"priority": "596efbfe-9129-4d51-a64c-33b0bc261f22",
	"Formula22": 1,
	"StoryPoint": null,
	"file_transfer": ["安装ftp客户端"],
	"r_man_hour_plugin_date": 1676304000000,
	"r_man_hour_plugin_type": 1,
	"r_man_hour_plugin_overHours": 1,
	"r_man_hour_plugin_normalHours": 1
}

• 查询values中包含man_hour字符串
  select * from "Item" where "values"::text like '%"man_hour"%';
• 查询指定某个字段等于某个值
  select * from "Item" where ("Item".values->>'r_man_hour_plugin_date')::int8 = 1679500800000;
• 查询指定某个字段大于某个值
  select * from "Item" where ("Item".values->>'r_man_hour_plugin_date')::int8 > 1679500700000;
• 查询r_man_hour_plugin_date
  select values->>'DataQuotecrtttt', values->>'remote_data_quote_field_type123' from "Item"
  假设数据为[{“status”: “approved”, “comment”: “”, “approver”: “I719aTBelK”, “approvalTime”: 1697018720199}]
• 查询approvalTime
  SELECT aa->0->'approvalTime' AS approvalTime FROM your_table;
• 多层嵌套json查询
  SELECT * FROM my_table WHERE (my_table.item -> 'itemType' ->> 'objectId')::int = 1;
SELECT * FROM my_table WHERE (my_table.item ->> 'itemType')::json ->> 'objectId' = '1';