Oracle数据库从BLOB类型JSON字段提取指定值异常求助

我数据库里有一张prov表，其中data_json列是BLOB类型，存储JSON格式数据，目前存在两种JSON结构（后续可能新增更多）。我需要提取entity节点下指定的type值：

第一种JSON（type1）示例 #

{
    "agent": {
        "iss:02228ba5-554d-4db7-802b-89ff360f2315": {
            "iss:idcode": "000005",
            "idd:type": "idd:org"
        }
    },
    "entity": {
        "iss:754df246-e3f7-46f6-b53c-f6f2770177f6": {
            "iss:algoritme1": "d5ad30e753204063bf15aea24805d2c3",
            "idd:type": "type1",
            "iss:algoritme2": "20ea978f31a14c7bac1415a9d3a50195",
            "iss:identifier": "test1"
        }
    }
}

第二种JSON（type2）示例 #

{
    "entity" : {
        "iss:132b7a2c-e598-419a-a3a8-6adb4aa86d6b" : {
            "idd:type" : [ "type2" ],
            "iss:identifier" : [ "test2" ]
        },
        "iss:fc36e29c-8ce9-4f3e-a8f9-fa4b32d5d2f0" : {
            "idd:value" : [ "23aeb0dd598f49c9b8fd065196724220" ],
            "iss:algoritme" : [ "algoritme1" ],
            "idd:type" : [ "tdd" ]
        },
        "iss:b53ae8ca-df09-4a66-9727-6f8a9bf1afcb" : {
            "idd:value" : [ "65d3d05ce8bc4a35b2a5dd96e377a3d8" ],
            "iss:algoritme" : [ "algoritme2" ],
            "idd:type" : [ "tdd" ]
        }
    },
    "agent" : {
        "iss:818c5dff-f08d-4831-b750-4887a10f1a50" : {
            "idd:type" : [ { "type" : "idd:NAME", "$" : "idd:org" } ],
            "iss:idcode" : [ "000005" ]
        }
    }
}

我使用以下查询语句：

SELECT id,
       JSON_VALUE(data_json, '$.entity.*."idd:type"[0]') type,
       data_json
FROM prov p;

查询结果中，type1能正确返回type1，但type2返回null（预期应为type2）：

补充说明：type2的JSON中entity节点下的元素顺序不固定、数量可能增加，但"idd:type" : [ "type2" ]（后续可能为type3等）只会出现一次，请问问题出在哪里？

问题根源 #

你使用的JSON_VALUE函数搭配通配符$.entity.*的写法存在局限性：

• 对于type1的JSON，entity下只有一个子节点，路径$.entity.*."idd:type"[0]仅匹配到一个值（且idd:type是字符串类型，数据库会隐式将其视为单元素数组取第一个值），所以能正常返回结果。
• 对于type2的JSON，entity下有多个子节点都包含idd:type字段，该路径会匹配到多个结果（type2、tdd、tdd）。而多数数据库的JSON_VALUE函数仅支持返回单个明确值，当匹配到多个结果时会直接返回null，这就是type2返回null的核心原因。

解决方案 #

需要用JSON表解析函数（不同数据库语法略有差异）展开entity下的所有子节点，再筛选出目标type值：

针对MySQL（8.0+） #

使用JSON_TABLE解析并筛选：

SELECT 
    p.id,
    j.type_val AS type,
    p.data_json
FROM prov p
JOIN JSON_TABLE(
    CAST(p.data_json AS JSON),
    '$.entity.*' COLUMNS (
        type_val VARCHAR(255) PATH '$.idd:type[0]'
    )
) j
WHERE j.type_val NOT IN ('tdd')

如果后续目标type有统一规则（比如以type开头），可将WHERE条件改为j.type_val LIKE 'type%'，适配更多类型。

针对SQL Server #

使用OPENJSON解析并筛选：

SELECT 
    p.id,
    j.type_val AS type,
    p.data_json
FROM prov p
CROSS APPLY OPENJSON(CAST(p.data_json AS NVARCHAR(MAX)), '$.entity')
CROSS APPLY OPENJSON(value)
WITH (
    type_val VARCHAR(255) '$.idd:type[0]'
) j
WHERE j.type_val NOT IN ('tdd')

关键注意点 #

1. 由于data_json是BLOB类型，需要先转换为JSON/字符串类型（如CAST(data_json AS JSON)或CAST(data_json AS NVARCHAR(MAX))）才能被JSON函数处理。
2. 利用目标type只会出现一次的特性，通过WHERE条件精准筛选，避免返回多余结果。

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