从Oracle数据库中的JSON Blob打印值

g6baxovj 于 2023-05-06 发布在 Oracle

关注(0)|答案(2)|浏览(190)

在数据库的表（prov）中，有一个BLOB类型的列（data_json），其中包含JSON类型的数据。
JSON结构有两种类型（可以更多，但现在只有两种）。
对于当前的两个类型，我尝试打印一个特定的值，该值是实体中的类型。
第一个json（type 1）的例子：

{
    "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"
        }
    }
}

secound json（type 2）示例：

{
    "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;

我得到了type 1的正确输出，但type 2的输出为null（应该是type 2）
| ID|类型|DATA_JSON|
| --------------|--------------|--------------|
| 四四七二|类型1|（BLOB）|
| 四七九二|（空）|（BLOB）|
你知道这是什么问题吗？
更新----------------------------------------------
第二个json（type 2）可以以不同的顺序出现，或者在实体中包含更多的元素：但是**“idd：type”：[“type 2”]**将只出现一次，但它也可以在某个时候是type 3。

{
    "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" ]
        }
    }
}

oracle

来源：https://stackoverflow.com/questions/76165436/print-value-from-json-blob-in-oracle-database

2条答案

按热度按时间

rks48beu1#

这是因为
SQL/JSON函数JSON_VALUE在JSON数据中查找指定的标量JSON值，并将其作为SQL值返回。
但是$.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;

ID	类型_
四四七二	“类型1”
四七九二	[“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;

ID	类型_
四四七二	类型1
四七九二	类型2

fiddle

赞(0）回复(0）举报 2023-05-06

ckocjqey2#

对于第二个JSON，您有多个iss:*值，因此其中有多个idd:type值。默认情况下，查询返回NULL ON ERROR，因此您看到的结果为null：

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

ID	类型
四四七二	类型1
四七九二	联系我们

但是如果你添加ERROR ON ERROR，你会看到：

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

ORA-40470: JSON_VALUE evaluated to multiple values

由于iss:*元素是独立的，不是数组的一部分，因此我认为您不能真正以其中一个为目标。例如，您不能执行'$.entity.*."idd:type"[0]'-这会得到相同的错误。
您可以使用JSON_TABLE来获取所有类型值：

SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*."idd:type"'
  COLUMNS (
    type PATH '$[*]'
  )
) j

ID	类型
四四七二	类型1
四七九二	类型2
四七九二	TDD
四七九二	TDD

...如果你只想要“第一个”，你可以添加并过滤ORDINALITY值：

SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*."idd:type"'
  COLUMNS (
    rn FOR ORDINALITY,
    type PATH '$[*]'
  )
) j
WHERE j.rn = 1

ID	类型
四四七二	类型1
四七九二	类型2

它假定您关心的类型值总是父实体元素中的第一个。
或者，选择一个也有iss:identifier的，如果这是一个有效的检查（我只是从样本数据猜测）：

SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*'
  COLUMNS (
    type PATH '$."idd:type"[*]',
    identifier PATH '$."iss:identifier"[*]'
  )
) j
WHERE j.identifier is not null

ID	类型
四四七二	类型1
四七九二	类型2

fiddle

赞(0）回复(0）举报 2023-05-06

我来回答

从Oracle数据库中的JSON Blob打印值

2条答案

相关问题

热门标签

最新问答