Oracle Select From JSON Array

Question

我有一个表的一些'设置'，在这个表中我有一个json数组的记录。它是一个简单的数组，如下所示:

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

我想在视图中使用子选择语句来提取这些信息，所以我可以这样使用它:

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

我一直在通过文档和谷歌搜索这个，但对于我的生活，我不能弄清楚如何"枢轴"返回数组到单个元素，以便使用它。

Oracle 12c我相信，提前感谢。

Answer 1

DoNOT使用正则表达式解析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;

输出:

ID | FIELD_SCENARIO-: |:----------------1 |场景12 |场景23 |场景35 |场景7 | scenario

db<此处小提琴>