PostgreSQL查询JSON/JSONB字段：提取符合条件的name值

假设存在表jira_components，其中component_data字段为JSONB类型（JSON类型操作逻辑一致，JSONB性能更优），存储的JSON数据结构如下：

[
{
"id": "234",
"name": "JCR",
"self": "https://jira.abc.io/jira/rest/api/2/component/234"
},
{
"id": "123",
"name": "React",
"self": "https://jira.abc.io/jira/rest/api/2/component/123"
}
]

1. 提取所有name字段的值 #

使用jsonb_array_elements展开JSON数组，再通过->>操作符提取文本类型的name字段值：

SELECT elem->>'name' AS component_name
FROM jira_components,
     jsonb_array_elements(component_data) AS elem;

2. 带WHERE条件的name字段匹配查询 #

精确匹配 #

查询name等于指定值的条目，同时可提取其他关联字段：

SELECT elem->>'name' AS component_name,
       elem->>'id' AS component_id,
       elem->>'self' AS component_url
FROM jira_components,
     jsonb_array_elements(component_data) AS elem
WHERE elem->>'name' = 'React';

模糊匹配 #

查询name包含指定字符串的条目：

SELECT elem->>'name' AS component_name,
       elem->>'id' AS component_id
FROM jira_components,
     jsonb_array_elements(component_data) AS elem
WHERE elem->>'name' LIKE '%R%';

注：如果字段类型为JSON，只需将jsonb_array_elements替换为json_array_elements即可。

内容的提问来源于stack exchange，提问作者Dhawal Sharma