sql - 在 PL/SQL 中处理 JSON 数组
问题描述
我有一个 JSON 文档存储在 myjson 表的 doc 列中。
{
"totalResultsCount": 19,
"geonames": [
{
"geonameId": 2593109,
"fcode": "ADM1"
},
{
"geonameId": 3336899,
"fcode": "ADM1"
}, ...
当我运行查询时,
select j.doc.geonames.geonameId from myjson j where j.doc.geonames.fcode like '%ADM1%';
我得到了一组 ID。
[2593109,3336899,3114710,2521383,3336903,2593110,3336898,3336900,2593111,3336901,2519582,2593112,3336902,3336897,3117732,6362988,2513413,3115609,2593113]
如何读取此数组并使用 FOR LOOP 打印每个 ID?
解决方案
您可以JSON_TABLE()
在 Oracle 的 SQL 中使用包含 CROSS JOIN 的函数来解析 和 的各个列值,product
并fcode
分别在 SELECT-list 和 WHERE 条件中使用它们,如下所示:
SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_id, js.product
FROM myjson
CROSS JOIN
JSON_TABLE(
doc, '$.geonames[*]' COLUMNS (
product VARCHAR(100) PATH '$.geonameId',
fcode VARCHAR(100) PATH '$.fcode'
)
) js
WHERE js.fcode = 'ADM1'
更新:根据您对返回值的数组分配需求的评论评估以下内容:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
json_array_t OWA.VC_ARR;
BEGIN
FOR c IN
(
SELECT ROW_NUMBER() OVER (ORDER BY 1) - 1 AS row_id, js.product
FROM myjson
CROSS JOIN JSON_TABLE(
doc, '$.geonames[*]' COLUMNS (
product VARCHAR(100) PATH '$.geonameId',
fcode VARCHAR(100) PATH '$.fcode'
)
) js
WHERE js.fcode = 'ADM1' )
LOOP
json_array_t(c.row_id) := c.product;
DBMS_OUTPUT.PUT_LINE( 'value for json_array_t('||c.row_id||') is '||json_array_t(c.row_id) );
END LOOP;
END;
/
更新 2:您可以使用原始查询设置数组值,然后根据需要使用 FOR LOOP 提取该数组的所有成员:
DECLARE
l_str VARCHAR2(32767);
l_top_obj JSON_OBJECT_T;
l_dept_arr JSON_ARRAY_T;
l_dept_obj JSON_OBJECT_T;
l_emp_arr JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T;
BEGIN
SELECT j.doc
INTO l_str
FROM myjson j
WHERE j.doc.geonames.fcode LIKE '%ADM1%';
l_top_obj := JSON_OBJECT_T(l_str);
l_dept_arr := l_top_obj.get_array('geonames');
FOR i IN 0 .. l_dept_arr.get_size - 1
LOOP
l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T);
DBMS_OUTPUT.PUT_LINE('index : '||i||' - geonameId : ' || l_dept_obj.get_number('geonameId'));
END LOOP;
END;
/
推荐阅读
- codenameone - 代号一 - 将约束添加到 PickerComponent 日期
- unit-testing - 模拟 BsModalRef 进行单元测试
- ruby-on-rails - 无法加载此类文件 — sqlite3/sqlite3_native (LoadError) Ruby on Rails
- sql - 表上的选择性能缓慢
- python - tensorflow sess 不会自动关闭
- redux - 在 redux 状态下可以使用带有 'isFetching' 标志的 'hasFetched' 吗?
- python - 为缺失的天数创建包含按列分组的新行
- python - python s=input().count input().count函数在python中有什么作用?
- android - 我们是否支持 liblinphone 中的紧凑标头?
- solr - SolrCloud ConfigApi 和 configoverlay 区别