时间戳

首页 > 解决方案 > Oracle 从 JSON 数组中选择

sql - Oracle 从 JSON 数组中选择

问题描述

我有一个用于一些“设置”的表，在该表中我有一个带有 json 数组的记录。这是一个简单的数组，如下所示：

"['scenario1','scenario2','scenario3']"

我想在视图中使用子选择语句来提取此信息，以便我可以像这样使用它：

select * from table where field_scenario in (select ????? from settings_table where this=that)

我一直在查看文档并为此进行谷歌搜索，但是对于我的一生，我无法弄清楚如何将返回的数组“旋转”成单个元素以使用它。

我相信Oracle 12c，在此先感谢。

标签： sqljsonoracleoracle12c

解决方案

不要使用正则表达式来解析 JSON。使用正确的 JSON 解析器：

select *
from   table_name
where  field_scenario in (
  SELECT j.value
  FROM   settings_table s
         OUTER APPLY (
           SELECT value
           FROM   JSON_TABLE(
             s.json,
             '$[*]'
             COLUMNS(
               value VARCHAR2(50) PATH '$'
             )
           )
         ) j
)

其中，对于样本数据：

CREATE TABLE settings_table ( json CLOB CHECK ( json IS JSON ) );
INSERT INTO settings_table ( json ) VALUES ( '["scenario1","scenario2","scenario3"]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario5"]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario \"quoted\""]');
INSERT INTO settings_table ( json ) VALUES ( '["scenario2,scenario4"]');

CREATE TABLE table_name ( id, field_scenario ) AS
SELECT LEVEL, 'scenario'||LEVEL FROM DUAL CONNECT BY LEVEL <= 6 UNION ALL
SELECT 7, 'scenario "quoted"' FROM DUAL;

输出：

身份证 | FIELD_SCENARIO   
-: | :----------------
 1 | 场景1        
 2 | 场景2        
 3 | 场景3        
 5 | 情景5        
 7 | “引用”的场景

db<>在这里摆弄

推荐阅读