Postgres查找json属性具有特定值的行

Question

我的postgres表中有一列存储json值。它是一个文本列。json看起来像(shortenend(

{
"class": "Workflow",
"outboundWorkflows": [
{
"class": "OutboundWorkflow",
"outboundConnector": {
"name": "Push HTTP Single Person"
},
"name": "Push HTTP Single PersonWF"
},
{
"class": "OutboundWorkflow",
"outboundConnector": {
"name": "Push HTTP Single Person1"
},
"name": "Push HTTP Single Person1WF"
}
],
"threadcount": 1,
"inboundConnector": {
"name": "Pull JSON Persons"
},
"name": "Http Pull And Push"
}

现在我想获取表中的所有行，其中outboundConnector.name的值为"；推送HTTP单人"；。它应该不区分大小写。所以我也应该为"；推送http单个PERSON"；。

我是postgres中JSON函数的新手。

我的第一次尝试：

select json_extract_path_text(json_workflow::json, 'name') from deployed_workflow; 

我得到json中的所有名称。我只想要出站连接器的名称。我不知道我是否也可以在where子句中使用这个。

所以我想要outboundConnector属性中的所有名称->名称：

推送HTTP单人

推送HTTP单人1

来自查询。

如果可能的话，我只想得到outboundConnector.name与匹配的行的id

所以类似于：

Select id from deployed_workflows where json_workflow::json -> outboundConnector-> name ilike 'Push HTTP Single Person'

Answer 1

如果您只想要id或完整的行，那么如果您使用的是Postgres 12或更高版本，则可以使用JSON路径表达式。

select id
from deployed_workflows 
where json_workflow @@ '$.outboundWorkflows[*].outboundConnector.name like_regex "Push HTTP Single Person"'

这假设json_workflow是一个jsonb列(它应该是(。如果不是，则需要将其转换为jsonb

在线示例

Answer 2

如果您想要Id并希望查看整个json对象或仅查看相关outboundConnector的名称值，这是查询：

SELECT id ,
outboundconnector_name
--,json_workflow
FROM   (
SELECT id,
Jsonb_array_elements(value::jsonb)->'outboundConnector'->>'name' outboundconnector_name ,
json_workflow
FROM   deployed_workflows x,
Json_each(x.json_workflow::json) y
WHERE  KEY = 'outboundWorkflows') inline_view
WHERE  outboundconnector_name ilike '%Push HTTP Single Person%';

输出：

id |  outboundconnector_name
----+--------------------------
1 | Push HTTP Single Person
1 | Push HTTP Single Person1
(2 rows)

要获得整个JSON对象，只需取消对这段内容的注释"--，JSON_workflow"。

运行查询的表：

postgres=# select * from deployed_workflows;
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id            | 1
json_workflow | {"name": "Http Pull And Push", "class": "Workflow", "threadcount": 1, "inboundConnector": {"name": "Pull JSON Persons"}, "outboundWorkflows": [{"name": "Push HTTP Single PersonWF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Single Person"}}, {"name": "Push HTTP Single Person1WF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Single Person1"}}]}
-[ RECORD 2 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id            | 2
json_workflow | {"name": "Http Pull And Push", "class": "Workflow", "threadcount": 1, "inboundConnector": {"name": "Pull JSON Persons"}, "outboundWorkflows": [{"name": "Push HTTP Double PersonWF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Double Person"}}, {"name": "Push HTTP Double Person1WF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Double Person1"}}]}