如何编写SQL查询从JSON中提取图片等相关数据？

原始需求 #

需要从以下JSON对象中提取images、remarks，以及documentList数组内对象的id和value字段：

目标JSON：

{"images": ["https://bijnis.s3.ap-south-1.amazonaws.com/2487dc60-c3b5-4cf9-a3a2-34f73683ac5a.jpg"], 
"remarks": "done", 
"documentList": [{"id": "GST", "value": "GST"}]}

初始查询的问题 #

你提供的初始SQL存在两个关键错误：

1. 表名无效：from Jason Object不是合法的表名，需替换为实际存储该JSON数据的表名
2. 数组属性提取错误：documentList是数组类型，直接用$.documentList.id无法定位到数组内的对象属性，必须通过数组索引或数组展开操作提取

分数据库的正确写法 #

MySQL 场景 #

当documentList仅含单个元素时

可以通过数组索引直接提取，推荐使用->>自动去除字符串引号：

SELECT
    my_json_field->'$.images' AS images,
    my_json_field->>'$.remarks' AS remarks,
    my_json_field->>'$.documentList[0].id' AS document_id,
    my_json_field->>'$.documentList[0].value' AS document_value
FROM your_table_name;

也可以用标准的JSON_EXTRACT+JSON_UNQUOTE组合：

SELECT
    JSON_EXTRACT(my_json_field, '$.images') AS images,
    JSON_UNQUOTE(JSON_EXTRACT(my_json_field, '$.remarks')) AS remarks,
    JSON_UNQUOTE(JSON_EXTRACT(my_json_field, '$.documentList[0].id')) AS document_id,
    JSON_UNQUOTE(JSON_EXTRACT(my_json_field, '$.documentList[0].value')) AS document_value
FROM your_table_name;

当documentList含多个元素时

使用JSON_TABLE展开数组，实现一对多关联查询：

SELECT
    jt.images,
    jt.remarks,
    doc.id AS document_id,
    doc.value AS document_value
FROM your_table_name,
     JSON_TABLE(
         my_json_field,
         '$' COLUMNS(
             images JSON PATH '$.images',
             remarks VARCHAR(255) PATH '$.remarks',
             documentList JSON PATH '$.documentList'
         )
     ) AS jt,
     JSON_TABLE(
         jt.documentList,
         '$[*]' COLUMNS(
             id VARCHAR(255) PATH '$.id',
             value VARCHAR(255) PATH '$.value'
         )
     ) AS doc;

PostgreSQL 场景 #

当documentList仅含单个元素时

通过->访问JSON结构，->>提取字符串值：

SELECT
    my_json_field->'images' AS images,
    my_json_field->>'remarks' AS remarks,
    (my_json_field->'documentList'->0)->>'id' AS document_id,
    (my_json_field->'documentList'->0)->>'value' AS document_value
FROM your_table_name;

当documentList含多个元素时

使用jsonb_array_elements展开数组：

SELECT
    my_json_field->'images' AS images,
    my_json_field->>'remarks' AS remarks,
    doc->>'id' AS document_id,
    doc->>'value' AS document_value
FROM your_table_name,
     jsonb_array_elements(my_json_field->'documentList') AS doc;

内容的提问来源于stack exchange，提问作者Uttam Kumar Gupta