如何从表的JSON列数组中提取符合category='B'条件的JSON对象？

针对不同数据库，提供对应的查询方案，实现从JSON数组中筛选出category='B'的JSON对象：

PostgreSQL #

PostgreSQL支持jsonb_array_elements函数展开JSON数组，再筛选条件，最后生成目标对象：

SELECT 
  jsonb_build_object('my_value', elem->>'my_value', 'category', elem->>'category') AS target_obj
FROM 
  your_table,
  jsonb_array_elements(your_json_column::jsonb) AS elem
WHERE 
  elem->>'category' = 'B';

如果每行仅存在一个category='B'的对象，也可以用jsonb_path_query简化查询：

SELECT 
  jsonb_path_query(your_json_column::jsonb, '$[*] ? (@.category == "B")') AS target_obj
FROM your_table;

MySQL #

MySQL使用JSON_TABLE展开数组并提取字段，再筛选匹配项：

SELECT 
  JSON_OBJECT('my_value', jt.my_value, 'category', jt.category) AS target_obj
FROM 
  your_table,
  JSON_TABLE(
    your_json_column,
    '$[*]' COLUMNS (
      my_value INT PATH '$.my_value',
      category VARCHAR(10) PATH '$.category'
    )
  ) AS jt
WHERE jt.category = 'B';

也可以通过JSON_SEARCH定位索引后直接提取：

SELECT 
  JSON_EXTRACT(
    your_json_column,
    CONCAT('$[', JSON_UNQUOTE(JSON_SEARCH(your_json_column, 'one', 'B', NULL, '$[*].category')), ']')
  ) AS target_obj
FROM your_table;

注：JSON_SEARCH的'one'参数表示仅返回第一个匹配项，若需提取所有匹配项可改为'all'。

SQL Server #

SQL Server通过OPENJSON解析数组，筛选后返回目标JSON对象：

SELECT 
  JSON_QUERY(jt.value) AS target_obj
FROM 
  your_table
CROSS APPLY 
  OPENJSON(your_json_column) AS jt
WHERE 
  JSON_VALUE(jt.value, '$.category') = 'B';

说明：上述示例中your_table为你的表名，your_json_column为存储JSON数组的列名，请根据实际情况替换。

内容的提问来源于stack exchange，提问作者Bjarke Kingo