postgresql - 如何编写 Postgres JSONB Where clausule
问题描述
我有下表:
CREATE TABLE mbiz.dictionary_groups (
slgr_id jsonb NOT NULL,
stored jsonb NOT NULL,
modified_date timestamp NOT NULL DEFAULT now(),
CONSTRAINT dictionary_groups_pkey PRIMARY KEY (slgr_id)
);
并将 json 对象保存在名为“已存储”的列中,例如:
{
"Position":
{"RotationId": 0, "SubGroupId": 0, "DiscoutGroupId": 99, "PriceIntervalId": 0},
"DefaultValue": 0.0,
"PositionValues":
[
{"Value": 26.0, "ProfileId": 1},
{"Value": 18.0, "ProfileId": 2},
{"Value": 33.0, "ProfileId": 12}
]
}
我正在尝试查找“PositionValues”中任何记录的“ProfileId”等于 2 的所有记录。
这是一个 Postgres 9.5,我发现了一些用户建议使用的提示,?
或者@>
当我尝试它时,我收到了错误消息:
“SQL 错误 [42883]:错误:运算符不存在:@> 未知
提示:没有运算符与给定名称和参数类型匹配。您可能需要添加显式类型转换。
位置:85”
解决方案
您需要在右侧提供一个数组:
select *
from dictionary_groups
where "stored" -> 'PositionValues' @> '[{"ProfileId": 2}]';
推荐阅读
- python - Python混淆矩阵
- sql - How can select all columns except some columns in SQL Server 2017?
- python - Spyder not opening the webcam result window using opencv even code is running
- asp.net-core - ASPNET Core 3.x - Appending data to all API responses
- go - How to add quotes to gval expression
- windows - Error when trying to run Hadoop after a new install
- python - Why can not access the uwsgi server with ERR_EMPTY_RESPONSE response?
- c - Saving two different structures in binary files and read it
- python - Python grab all UDP Datagrams
- c# - Raise an Event Except when something happpen