我有一个表,我在其中分配了一个JSON文档作为CLOB字段中的字符串。在JSON中,有一个数组,我需要将它公开为一个表,以便可以连接到SQL语句中。
让我举一个例子来解释一下:在下面的JSON中,我需要将一个包含两列(type和main)和两行(数组长度相同)的表中的数组转换为可以与另一个table. field连接。
JSON示例
{
"string":"string",
"array":[{
"type":"main",
"name":"name"
},{
"type":"othertype",
"name":"othername"
}],
"object":{
"type":"objecttype",
"name":"objectname"
}
}
我阅读了官方文档,发现我可以用JSON_TABLE函数来实现。文档中有两个不同的JSON_TABLE,一个在SYSIBM包的内置函数上,另一个在SYSTOOLS包上,似乎将来会被弃用。
使用SYSIBM.JSON_TABLE函数,我可以从JSON字段中提取属性,例如字符串或对象内部的字符串,但我不能将数组提取为表行(我的主要目标)。
以下是我尝试执行的SQL语句(我将json作为参数进行检查,但最终目的地是在table.field上):
select t.*
from json_table('{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' FORMAT JSON,
'strict $' columns (
string varchar(20) path 'strict $.string',
type varchar(20) path 'strict $.object.type',
name varchar(20) path 'strict $.object.name',
nested path 'strict $.array[*]' columns(
type2 varchar(20) path 'strict $.type',
name2 varchar(20) path 'strict $.name'
)
) error on error
) as t where true;
我得到的错误是:
SQL0104N An unexpected token "path 'strict $.array[*]' columns(type2" was found following "object.name', nested". Expected tokens may include:"<space>". SQLSTATE=42601
预期结果:
| 弦|类型,类型|姓名|类型2|名称2|
| - -----|- -----|- -----|- -----|- -----|
| 弦|对象类型|对象名|主要|姓名|
| 弦|对象类型|对象名|其他类型|别名|
如果我为特定索引更改[*]子句,即。[0]工作,但只接收数组中的第一个元素,如果没有元素则抛出错误:
select t.*
from json_table('{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' FORMAT JSON,
'strict $' columns (
string varchar(20) path 'strict $.string',
type varchar(20) path 'strict $.object.type',
name varchar(20) path 'strict $.object.name',
nested path 'strict $.array[1]' columns(
type2 varchar(20) path 'strict $.type',
name2 varchar(20) path 'strict $.name'
)
) error on error
) as t where true;
| 弦|类型,类型|姓名|类型2|名称2|
| - -----|- -----|- -----|- -----|- -----|
| 弦|对象类型|对象名|主要|姓名|
此外,如果我从SQL语句中删除数组规范,它不会抛出任何错误,但很明显,我没有需要的数据:
select t.*
from json_table('{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' FORMAT JSON,
'strict $' columns (
string varchar(20) path 'strict $.string',
type varchar(20) path 'strict $.object.type',
name varchar(20) path 'strict $.object.name'
) error on error
) as t where true;
获得的结果:
| 弦|类型,类型|姓名|
| - -----|- -----|- -----|
| 弦|对象类型|对象名|
**注意:**我也检查了SYSTEOLS.JSON_TABLE,但它并不像我想要的那样工作,并且还需要一个BSON作为主源代码,而不是字符串,我不想每次我需要查看内部代码时都进行转换。
**NOTE2:**此外,当我尝试将 null放在error 而不是 error on error(返回null值而不是错误)时,我收到了一个错误。当我用 lax 更改 strict 子句时,也会发生同样的情况。
1条答案
按热度按时间xv8emn3q1#
Db2 for LUW(包括当前的v11.5.8.0)不支持
nested path
表达式。看看这个解决方案,它有一个通用函数here。
在您的案例中:
结果是:
| 字符串|类型|名称|类型2|名称2|
| - -----|- -----|- -----|- -----|- -----|
| 弦|对象类型|对象名|主要|姓名|
| 弦|对象类型|对象名|其他类型|别名|