json - 从oracle数据库中的json blob打印值

Question

在我的数据库的表 (prov) 中，我有一个 BLOB 类型的列 (data_json)，其中包含 JSON 类型的数据。
JSON 结构有两种类型(可以更多，但现在只有两种)。

对于当前的两种类型，我尝试打印一个特定值，即实体内的类型。

第一个 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 的正确输出，但 type2 的输出为 null(它应该是 type2)

<表类=“s-表”><标题>ID类型DATA_JSON <正文>4472类型1(BLOB)4792(空)(BLOB)

知道这里有什么问题吗？

更新----------------------------------------------

第二个 json (type2) 可以以不同的顺序出现，或者在实体中包含更多元素:但是 "idd:type": [ "type2"] 只会出现一次，但在某些时候它也可以是 type3。

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

Answer 1

这是因为

The SQL/JSON function JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a SQL value.

但是$.entity.*."idd:type"[0]指向一个字符串数组。您需要使用json_query为此:

select
  id,
  json_query(
    data_json,
    '$.entity.*."idd:type"[0]'
    with conditional array wrapper
  )as type_
from prov p;

<表类=“s-表”><标题>IDTYPE_ <正文>4472"类型1"4792["type2","tdd","tdd"]

您可以使用另一个 json_value 提取此数组的第一个元素功能:

select
  id,
  json_value(json_query(
    data_json,
    '$.entity.*."idd:type"[0]'
    with conditional array wrapper
  ), '$[0]') as type_
from prov p;

<表类=“s-表”><标题>IDTYPE_ <正文>4472类型14792类型2

fiddle