如何从SQL表的report_json JSON列提取所有药物的bin与name字段

以下是不同主流SQL数据库的实现方案：

MySQL（5.7及以上版本） #

使用JSON_TABLE和JSON_KEYS解析嵌套JSON对象：

SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(drug_info, '$.bin')) AS bin,
  JSON_UNQUOTE(JSON_EXTRACT(drug_info, '$.name')) AS name
FROM 
  your_table_name,
  JSON_TABLE(
    JSON_KEYS(report_json->'$.drugs'),
    '$[*]' COLUMNS(drug_key VARCHAR(255) PATH '$')
  ) AS keys,
  JSON_TABLE(
    report_json->'$.drugs."${drug_key}"',
    '$' COLUMNS(drug_info JSON PATH '$')
  ) AS drugs;

PostgreSQL（支持json/jsonb类型） #

使用jsonb_each展开JSON对象：

SELECT 
  (drug_data->>'bin') AS bin,
  (drug_data->>'name') AS name
FROM 
  your_table_name,
  jsonb_each(report_json::jsonb->'drugs') AS drugs(drug_name, drug_data);

SQL Server（2016及以上版本） #

使用OPENJSON指定解析路径：

SELECT 
  JSON_VALUE(drug_data, '$.bin') AS bin,
  JSON_VALUE(drug_data, '$.name') AS name
FROM 
  your_table_name
CROSS APPLY 
  OPENJSON(report_json, '$.drugs')
  WITH (drug_data NVARCHAR(MAX) AS JSON);

输出示例 #

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