Oracle and multiple JSONs

Question

如何在oracle中获得'id '列表如果数据是这样的?

{'id': 360006648459, 'value': None
},
{'id': 360002406974, 'value': None
},
{'id': 360003271394, 'value': None
},
{'id': 360020289239, 'value': None
}
...

我得到(null)时，尝试这是预期的:

SELECT json_query(FIELD, '$.id')
FROM TABLE

Answer 1

select json_value(q'~{'id': 360006648459, 'value': None}~', '$.id') from dual ;

将返回id

select json_query(q'~{'id': 360006648459, 'value': None}~', '$.id' with wrapper) from dual ;

将返回包装成数组的id

Answer 2

如果您有非json样例数据:

CREATE TABLE table_name (field) AS
SELECT q'|{'id': 360006648459, 'value': None
},
{'id': 360002406974, 'value': None
},
{'id': 360003271394, 'value': None
},
{'id': 360020289239, 'value': None
}|' FROM DUAL;

然后你可以尝试转换为有效的JSON通过包装它在数组[]括号和改变None到null，然后你可以使用JSON解析器:

select j.id
from   table_name
CROSS APPLY JSON_TABLE(
'[' || REPLACE( field, 'None', 'null') || ']',
'$[*]'
COLUMNS (
id path '$.id'
)
) j;

注意:这假设子字符串None只会出现在表示null值的上下文中;如果它可以出现在其他上下文中，那么您可能无法使用此方法。

注2:从技术上讲，对象键周围的单引号不是有效的JSON;然而，Oracle会把它们当作有效的引号来解析。要转换为严格的JSON格式，您需要'[' || REPLACE( REPLACE( field, 'None', 'null'), '''', '"') || ']'.

输出:

ID360006648459360002406974360003271394360020289239

假设您有一个有效的json，如'{"id": 360006648459, "value": "None"},{"id": 360002406974, "value": "None"},{"id": 360003271394, "value": "None"},{"id": 360020289239, "value": "None"}'

然后你可以使用json_table

得到你想要的输出

select id
from mytable, json_table(concat(concat('[', FIELD),']'), '$[*]' columns(id path '$.id')) t

演示