使用Oracle SQL无法通过json_array和json_objects创建嵌套JSON

你的SQL存在语法和结构错误，导致无法生成预期的JSON，以下是修正后的完整语句：

select 
       json_object('Id' VALUE ilv.id,
                   'arrList' VALUE JSON_ARRAY(
                         JSON_OBJECT('name' VALUE 'Quantity', 'value' VALUE ilv.Quantity),
                         JSON_OBJECT('name' VALUE 'SupplierQty', 'value' VALUE ilv.SupplierQty),
                         JSON_OBJECT('name' VALUE 'Supplier', 'value' VALUE ilv.Supplier)                         
                                    ),   
                    'item' VALUE ilv.item,  
                    'quantity' VALUE JSON_OBJECT('UnitCode' VALUE 'EA', 'value' VALUE ilv.Quantity)           
        )AS json_detail
  from 
(select '12345' item,
       12 projqty,
       0 Quantity,
       0 SupplierQty,
       'MK' Supplier,
       sys_guid() id
  from dual)ilv

关键修改点说明： #

• 修正顶级键名：将'id'改为'Id'，匹配预期JSON的首字母大写格式。
• 补全arrList内的JSON对象键：第二个和第三个JSON_OBJECT中缺少'value'关键字，必须明确指定'value'键对应字段值，否则会触发语法报错。
• 修正quantity对象结构：原语句错误使用'name'作为键，改为直接指定'UnitCode'键并赋值为'EA'，同时将value对应的值调整为ilv.Quantity（匹配预期的0，若需使用projqty可自行替换）。
• 移除冗余字段：外层SELECT中的item,id属于重复输出，已包含在生成的JSON内，可直接删除。

执行修正后的语句后，将生成你预期的嵌套JSON结构。

内容的提问来源于stack exchange，提问作者B-Rad